Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Offset/Match (Excel 2000)

    Attached is a snippet of what I'm trying to accomplish.

    I want the SUMMARY sheet to look (like VLOOKUP) to the left, (say A5) and MATCH the Dr. Name then Match the Week number in (say Col. B4) to the Physician Log and WHERE it finds a MATCH return the appropriate "YTD" value. I've worked on some of the formula...but the way it is right now....each time a Doc's name or a new week is added the formula would have to be modified rather than copy and paste special. However, if that's what I'm left with so-be-it.....hoping not though....

    Does anyone have any suggestions?

    Thanks for any and all help.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    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: Offset/Match (Excel 2000)

    some options:
    Use range names:
    Name the weeks (eg)
    Week_1 ='physician Log'!$A$3:$L$20
    Week_2 ='physician Log'!$A$23:$L$38
    Week_3 ='physician Log'!$A$41:$L$56
    etc
    In B6 of MTH summary, enter in:
    <pre>=VLOOKUP($A6,INDIRECT("week_"&B$4),12,FALSE )</pre>


    This can be copied down the rows and across the columns for the table

    Add new week names as needed

    Another option is to revamp/transpose and combine the spreadsheet with the DATES in 1 column rather than sections with the NAMES in columns. It would be easier to manipulate on many levels.

    If you insert new rows for new physicians the ranges will automatically expand, though you will need to create named ranges for each week.

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Offset/Match (Excel 2000)

    Steve,

    Thanks for the suggestions. The latter can't be accomplished as you suggested as this isn't my spreadsheet...IT'S THE BOSS' ...his creation...I'm trying to find a better method of maintaining it.

    I like the first suggestion, but I'd like to be clear what it's doing. I follow the naming ranges and most of the VLookup function...where I'm lost is the constant $B$4 --not clear on Indirect and what is "12" (is it the col # to look in ?) in the statement? Could you, if you have the time and don't mind, explain the function for me, I would appreciate it. I know it works, I've tested it....Thanks....I'd just like to understand it also.

    Thanks.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Offset/Match (Excel 2000)

    The $B4 picks up the week number from the fourth row of your Summary sheet and appends it to the string "Week_" to form the name that references the correct table on the log sheet. The Indirect function converts that string ("Week_1". "Week_2", etc.) into a reference that the VLookUp function needs for its array argument. The 12 tells VLookUp to return the 12th column in the lookup table.
    Legare Coleman

Posting Permissions

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