Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    last entered data accross 2 worksheets (2000)

    I use the following formula to retrieve the last entetred data in a row:
    {=INDEX(A1:Z1,MATCH(9.9999999999E+307,A1:Z1))}
    My question is what would be the formula to retrieve the last data entry in a row that transverses 2 worksheets (i. e., begins on the first and continues accross to the 2nd)?
    Thanks,
    Jeff

  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: last entered data accross 2 worksheets (2000)

    Try thisone line, ARRAY)
    =IF(ISNA(MATCH(9.9999999999E+307,Sheet2!1:1)),INDE X(Sheet1!1:1,MATCH(9.9999999999E+307,Sheet1!1:1)), INDEX(Sheet2!1:1,MATCH(9.9999999999E+307,Sheet2!1: 1)))

    Sheet1 is the first sheet, sheet2 the continuation. If Nothing is on Sheet2 it generates a #N/A error so the first page is looked, otherwise the 2nd page is looked.

    Steve

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: last entered data accross 2 worksheets (2000)

    Steve,
    This looks like it will work-what if I have >2 worksheets (say, 12)?
    Thanks,
    Jeff

  4. #4
    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: last entered data accross 2 worksheets (2000)

    This method is limited to 7 embedded ifs.

    The easiest way might be to setup in some summary sheet the 12 equations:
    =INDEX(Sheet1!1:1,MATCH(9.9999999999E+307,Sheet1!1 :1))
    =INDEX(Sheet2!1:1,MATCH(9.9999999999E+307,Sheet2!1 :1))
    ...
    =INDEX(Sheet12!1:1,MATCH(9.9999999999E+307,Sheet12 !1:1))
    Across 12 columns (eg B1-M1)
    Then in A1 enter the equation:
    =INDEX(B1:M1,MATCH(9.9999999999E+307,B1:M1))

    This will find the last value assuming that ALL 12 sheets are to be treated like 1 continuous row.

    Since each sheet is limited to 256 columns, why don't you transpose them. Then you can have 1 continuous column of 65,536 values (on ONE SHEET) so you can EASILY handle the 3,000+ entries you are talking about without all this rigamarole.

    Steve

Posting Permissions

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