Good evening everyone,

I've been messing with different indirect and lookup formulas to pull the content of the last cell (formatted as "20180504 15:43:31:612") of column A from multiple sheets to populate a column of a master front sheet.

Using column A in the master sheet, I have created the list of all the sheets numbered 043 to 179 as a reference, I then used this formula =INDIRECT("'"&A1&"'!A2") (the first populated cell in column A of every sheet starts at A2) to get the first cell of all sheets to populate in column B as so -

043 20180504 15:41:22:515
044 20180504 18:45:24:781
045 20180504 18:56:55:340
046 20180504 19:19:33:802
047 20180504 20:13:03:752
048 20180505 05:26:50:697
049 20180505 08:17:22:718
050 20180506 20:23:39:871
051 20180506 20:44:53:428
052 20180507 06:03:58:207

The same formula does not work to find the last cell, as each last cell is a different row number through all the sheets and when tried to add a lookup to replace A2 I kept messing up and getting errors.


I used this formula =LOOKUP(2,1/('043'!A:A>0),'043'!A:A) to find the last cell of column A of the first sheet which worked, but when I try to drag to populate sequentially down the column, the sheet reference does not increase, i.e.

=LOOKUP(2,1/('044'!A:A>0),'044'!A:A)
=LOOKUP(2,1/('045'!A:A>0),'045'!A:A)

Etc

When I try to add the same cell reference "'"&A1 that is in column A, 043, 044, 045 etc, I keep getting errors.

I'm hoping I'm not far off but have hit the limitations of what I can do and I hope all this makes sense.

Any help will be gratefully received.

Thanks