Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lookup in Excel 2010

    I have attached a spreadsheet with two tabs. Tab 1 contains weekly sheets with data that is totaled at the bottom of the day columns. The weekly sections are not linear, the weeks are stacked on top of each other. Tab 2 contains a sheet that has the day of the month at the top of the column (these will actually be hidden once the sheet is complete. I have hi-lighted the fields that I want to fill in Tab 2 with the hi-lighted field in Tab 1. Is there a way to auto-fill the data in Tab 2 once the data is entered in Tab 1, without having to just manually use = this cell. Is there a Lookup function that will look at the date in Tab 2, then find the date in Tab 1 and return the value of the field 10 cells below that date?
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    I can't see the highlighted fields you mention.
    Your tab2 sheet dates don't correspond to any of the dates in tab1 sheet.
    Can you check your posted file again.

    zeddy

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry the yellow hi-ligthing of the cells did come across on the download. Should have put cell references anyway. Here they are.
    In Tab 2, Cell C3 is the cell that I want populated automatically, by using the date in C2. Find that date in Tab 1 (in this case it appears in C3) and return the figure in the cell that is 10 rows below (cell C13). I have also sent an updated sheet.
    Attached Files Attached Files

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi

    Need a little more info:
    Q1: Will there be lots and lots of weekly sections on sheet [Tab 1] or will there always be just 4??

    Q2: On sheet [Tab 2], will cell [B2] always be the same value as cell [C3] on sheet [Tab 1]??

    zeddy

  5. #5
    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
    In Tab2!C3 enter the mega formula:

    =OFFSET('Tab 1'!$A$1,IF(ISNUMBER(MATCH(F2,'Tab 1'!$C:$C,0)),MATCH(F2,'Tab 1'!$C:$C,0),0)+IF(ISNUMBER(MATCH(F2,'Tab 1'!$D:$D,0)),MATCH(F2,'Tab 1'!$D:$D,0),0)+IF(ISNUMBER(MATCH(F2,'Tab 1'!$E:$E,0)),MATCH(F2,'Tab 1'!$E:$E,0),0)+IF(ISNUMBER(MATCH(F2,'Tab 1'!$F:$F,0)),MATCH(F2,'Tab 1'!$F:$F,0),0)+IF(ISNUMBER(MATCH(F2,'Tab 1'!$G:$G,0)),MATCH(F2,'Tab 1'!$G:$G,0),0)+IF(ISNUMBER(MATCH(F2,'Tab 1'!$H:$H,0)),MATCH(F2,'Tab 1'!$H:$H,0),0)+IF(ISNUMBER(MATCH(F2,'Tab 1'!$I:$I,0)),MATCH(F2,'Tab 1'!$I:$I,0),0)+9,ISNUMBER(MATCH(F2,'Tab 1'!$C:$C,0))*COLUMN($C1)+ISNUMBER(MATCH(F2,'Tab 1'!$D:$D,0))*COLUMN($D1)+ISNUMBER(MATCH(F2,'Tab 1'!$E:$E,0))*COLUMN($E1)+ISNUMBER(MATCH(F2,'Tab 1'!$F:$F,0))*COLUMN($F1)+ISNUMBER(MATCH(F2,'Tab 1'!$G:$G,0))*COLUMN($G1)+ISNUMBER(MATCH(F2,'Tab 1'!$H:$H,0))*COLUMN($H1)+ISNUMBER(MATCH(F2,'Tab 1'!$I:$I,0))*COLUMN($I1)-1)

    Copy it into the F, I, L, O13, etc

    Steve

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi

    You could use a shorter formula in Tab2C3:
    =IFERROR(OFFSET('Tab 1'!$A$1,(12*(INT((C2-'Tab 1'!$C$3)/7)+1)),MOD((C2-'Tab 1'!$C$3),7)+2),0)

    ..and copy to other cells as required (including [C5], [E5] etc)

    This will work provided the week blocks on sheet [Tab 1] are consistent in size and spacing, and that the dates in each block are sequential starting from whatever date is in cell [C3] on sheet [Tab 1].
    The error check in the formula simply returns a zero value if the date you are checking is earlier than the first date in cell [C3] on sheet [Tab 1]

    My formula eliminates the matching of dates, and instead, relies on the structure of the weekly blocks.
    My reason for avoiding using match with dates is essentially that Excel stores dates as a number e.g. today 29-Jan-2012 is 40937. Dates for this year 2012 fall in the range 40909 to 41274. So if you searched a column for todays date you would hope that there weren't any numbers like 40937 in the range that were data items etc.

    zeddy

  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
    A much better solution provided that the data is set up in the pattern. I also chose to NOT use the IFERROR in case the solution needed to be compatible with older (pre-XL2007) XLS versions. IFERROR was not introduced until XL2007

    Steve

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry so long since responding - I thought I had responded weeks ago, eveything worked fine and yes there was a pattern for a total of 53 weeks, for the whole year. Thanks again for your help.

Posting Permissions

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