+ Reply to Thread
Results 1 to 5 of 5

Using a formula to display the last populated cell in a row...over multiple sheets

  1. #1
    Registered User
    Join Date
    11-14-2012
    Location
    here
    MS-Off Ver
    Excel 2011
    Posts
    3

    Using a formula to display the last populated cell in a row...over multiple sheets

    Dear anyone who can help me,

    I have a workbook with multiple sheets. On 'Sheet 1' I have a formula in a cell that finds the last populated cell in the row and displays that information:

    e.g. E A B C D E

    (The bold letter above represents the cell with the formula. The letters after represent the following cells).

    Here is the formula: =INDEX(Q8:AE8, MATCH(REPT("z",255),Q8:AE8))

    It works perfectly.

    My problem is that I want the cell to display the last populated cell OVER MULTIPLE SHEETS.

    I have tried =INDEX(('Sheet 1'!Q9:AF9, 'Sheet 1a'!Q9:AF9), MATCH(REPT("z",255),('Sheet 1'!Q9:AF9, 'Sheet 1a'!Q9:AF9)))
    And also =INDEX('Sheet 1:Sheet 1a'!Q9:AF9, MATCH(REPT("z",255),'Sheet 1:Sheet 1a'!Q9:AF9))

    Am I attempting the impossible here? Any suggestions as to what may work in this situation?

    Any help will be very much appreciated.

  2. #2
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Using a formula to display the last populated cell in a row...over multiple sheets

    =IF(MATCH("zzzzz",'Sheet 1'!Q9:AF9)>MATCH("zzzzz",'Sheet 1a'!Q9:AF9),LOOKUP("zzzzz",'Sheet 1'!Q9:AF9),LOOKUP("zzzzz",'Sheet 1a'!Q9:AF9))

  3. #3
    Registered User
    Join Date
    11-14-2012
    Location
    here
    MS-Off Ver
    Excel 2011
    Posts
    3

    Re: Using a formula to display the last populated cell in a row...over multiple sheets

    Thank you for your response. Unfortunately this doesn't appear to work.

    I want the formula to check 'Sheet 1' for the last populated cell in the row and then check 'Sheet 1a' for the last populated cell in the row and display the last value. Please see my initial post for my best stab at this.

    Thanks.

  4. #4
    Registered User
    Join Date
    11-14-2012
    Location
    here
    MS-Off Ver
    Excel 2011
    Posts
    3

    Re: Using a formula to display the last populated cell in a row...over multiple sheets

    Ok I have solved this using nested IF statements and test events to check if the preceeding page has text written in the Q9:AF9 cells.

    =IF(SUMPRODUCT(ISTEXT('Sheet 1c'!Q9:AF9)*1),INDEX('Sheet 1c'!Q9:AF9,MATCH(REPT("z",255),'Sheet 1c'!Q9:AF9)),(IF(SUMPRODUCT(ISTEXT('Sheet 1b'!Q9:AF9)*1),INDEX('Sheet 1b'!Q9:AF9,MATCH(REPT("z",255),'Sheet 1b'!Q9:AF9)),(IF(SUMPRODUCT(ISTEXT('Sheet 1a'!Q9:AF9)*1),INDEX('Sheet 1a'!Q9:AF9,MATCH(REPT("z",255),'Sheet 1a'!Q9:AF9)),INDEX('Sheet 1'!Q9:AF9,MATCH(REPT("z",255),'Sheet 1'!Q9:AF9)))))))

    Thanks anyway!

  5. #5
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Using a formula to display the last populated cell in a row...over multiple sheets

    If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1