Pull the content of the last cell of a column from multiple sheets to a master front sheet

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

Answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

Good morning all,

apologies, new to this forum so didn't see those instructions.

I've now uploaded a small example workbook.

For context, the various sheets are drone flight data which ultimately feed into a flying log I need to keep as I fly commercially.

Try

in F2

=LOOKUP(2,1/(INDIRECT("'"&E2&"'!A:A")>0),INDIRECT("'"&E2&"'!A:A"))

John, thank you, you are a star! That's worked great. And now you've provided the answer, I can see where I was going wrong.

Thanks you again.

You're welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Originally Posted by kvsrinivasamurthy

In F2 then copy down.

=INDIRECT("'"&$E2&"'!A" &COUNTA(INDIRECT("'"&$E2&"'!A2:A2000"))+1)

kvsrinivasamurthy, thank you. I'm guessing I would replace the A2000 with the higher possible row number?

In the flight data, the rows can go upto 40000 per sheet.

Yes: I tested both formulae with 42000 rows and all was OK.

Great! Thank you both for your help.