Results 1 to 8 of 8

Thread: VLOOKUP (2000)

  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Kenton, Delaware, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VLOOKUP (2000)

    I have fooled with this thing for over a day and am very frustrated. On the attached workbook I have two sheets the first sheet called calendar starts in B2 with the formula Today() the next cell over has the formula b2+1 making it the next day and so forth accross the row. The other sheet is a table contaiing events. What I want to have happen is that under b2 or c2 or d2 lookup in the events table and list the events that match the date above it. Everytime I try to set up a lookup function and use the row 2 reference as a value it sums it and does not handle it as a date. Can anyone help me with this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VLOOKUP (2000)

    The dates and times in the Events worksheet all have an apostrophe ' before them (visible in the formula bar). This means that all these cells are interpreted as text, not as dates or times.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VLOOKUP (2000)

    To followup on Hans' message:
    To convert your "text" dates and times to real dates and times:
    put the number 1 into a cell. Copy it (ctrl-c)
    select your columns A-D
    edit paste-special multiply
    Now all your "text" items are numbers. Format as desired

    Bonus tip
    If col A nd col C are date and times that go together (and B & D go together) you could combine them into one column by simple additon:
    =A2+C2 (and/or =B2+D2)
    to yield a date and time together (this can be formatted: "mmm d, yyyy hh:mm am/pm" to display something like: "Nov 18, 2003 7:34 AM")

    The "date" is the integer portion, the "time" is the decimal portion. Time (in excel) is stored as a fraction of a day.
    Steve

  4. #4
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Kenton, Delaware, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP (2000)

    Assuming <G> I resolve that problem. What code do I use in the cell below the dates in Calendar that will list what ever matches that date on the events page. Just the subject not every column.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP (2000)

    Another way is to select each column in turn, Data Menu, Text to Columns, Finish.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VLOOKUP (2000)

    Yes that is another good way.

    But, your method only works on one column at a time. The method I proposed will work with the entire range in one operation.

    Steve

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VLOOKUP (2000)

    If your dates are not sorted (like your example) then:
    <pre>=VLOOKUP(B2,Events!$A$2:$E$67,5,FALSE)</pre>


    will give you the value. It must be an EXACT match to the date in col A (and if that is the case, do not add the time as I suggested earlier)

    If you do not want an exact match, but the "closest without being later" the dates in A must be sorted and you would use:
    <pre>=VLOOKUP(B2,Events!$A$2:$E$67,5)</pre>


    Steve

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP (2000)

    I guess it depends on how many columns you have to deal with, I always use the way I described as it doesn't involve writing and deleting a 1 to and from the sheet and it seems quicker, at least for a few columns. I seem to remember that someone, maybe Hans or Jan, wrote a macro that multiplied each cell in the selection by 1 to achieve the same result, but I don't have a copy of it, and without the search function.....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •