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

1. ## 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. ## 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. ## 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. ## 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. ## Re: Using a formula to display the last populated cell in a row...over multiple sheets

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