Last Value from Column on Different Sheet

[dagsvic]

Hi All,

This is driving me nuts coz i have found plenty of posts but i can not get them to do what i am after.
So Fingers Crossed i can have one of your brains work better than mine in a simple fix.

Sheet 1
====
Column B - Has Text that is the name of Sheets (eg. Joe Smith or Peter Blogg)
Column D - The Cell that i want to show the last value entered within "Sheet 2 - Column N3:N500".

** There will be blank cells within "Sheet 2 - Column N3:N500"
** N3:N500 are fixed and will NOT go beyond this.
** Best case scenario is that if no value within "Sheet 2 - Column N3:N500" that Cell D# on SHEET 1 would display value from "Sheet 2 - Cell N1"

Examples I have tried are:

** Seems to work --

Please Login or Register  to view this content.

** Seems to get the 2nd Last Value --

Please Login or Register  to view this content.

Sheet 2 or 3 or 4 etc aka Joe Smith or Peter Blogg
====
Column N (Cells 3 --> 500) - has Formulas as per below

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Can you help please?
The simpler the formula I can use the better.

Very much appreciate your assistance if you can help.

Thanks

[dflak]

It is difficult to visualize what you are trying to do from a verbal description. Including a sample spreadsheet would help a lot.

[JohnTopley]

Is column D text or numbers or both?

[Tony Valko]

See if this does what you want...

On Sheet1...

B2 = Joe Smith

D2 formula: =LOOKUP(1E100,INDIRECT("'"&B2&"'!N3:N500"))

That formula will return the last (bottom-most) number from the range 'Joe Smith'!$N$3:$N$500.