Indirect function to find last non blank cell in another worksheet

[WednesdayC]

Hi All

I am stuck with this and need some help please.

I have a sheet called Wednesday, and I am trying to find the last value (which will always be a number if it makes a difference) in column A of the worksheet. There will be blank cells in this column.

so far I have :-

=INDIRECT("'"&A1&"'!A11")

with A1 etc showing the name of the sheet - (in reality there are lots of sheets in row 1).

A11 contains the last non-blank entry in column A, but I am trying to make the 11 part dynamic, so the formula will look it up. I just can't work out how to do this.

A huge thanks, in advance for any assistance.

Wednesday

 

[Jonmo1]

Try

=LOOKUP(9.99999999999999E+307,INDIRECT("'"&A1&"'!A:A"))

 

[WednesdayC]

Hi Jonmo

Fantastic! It works a treat.

I did see something on the Internet along these lines, but was obviously doing something wrong.

Thank you so much.

Wednesday

 

[Jonmo1]

You're welcome.


What happened to Monday?

 

[WednesdayC]

Hi Jonmo

Ha ha!
Wednesday is my nickname - from the Adams family. Wednesday Adams!

Have a good rest of weekend.

Wednesday

 

[Jonmo1]

What happened to Monday.
It’s a movie on Netflix, you should check it out.

 

[WednesdayC]

Hi Jonmo

Will do - I looked it up and the cast is great.

Thanks for the tip and especially the formula.

Wednesday