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.

=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))

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.

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!