Summary Sheet lookup

[Gemandshed]

Hi

I’ve got a spreadsheet with multiple worksheets - all different peoples names

I’m trying to create a summary sheet to lookup each persons specific name and return the values within the worksheet

In the summary sheet I need column D to look for the persons worksheet and return the value in column E, but the last cell to have any value.

I hope this makes sense.

Regards
Gemma

 

[Gemandshed]

Thank you. I’ve been working on this this morning and I’ve got it to work. The reason it wasn’t working was to do with the name. If I removed the surname and changed the worksheet name to first name only it works but I need it to work with first name and surname. As too many of the same first name.
How can I do this please?
Thank you in advance

 

[awoohaw]

if the name is "John Smith" and the sheet name is John Smith, then you need to surround the sheet name reference with apostrophes.

=INDEX(INDIRECT("'"&A2&"'!$E$2:$E$100"),MAX((ROW(INDIRECT("'"&A2&"'!$E$2:$E$100"))-1)*(ISNUMBER(INDIRECT("'"&A2&"'!$E$2:$E$100")))))

And commit with CSE keystroke combination.

 

[Gemandshed]

Thank you so much it’s working
Just one last question
What do I change if I want the formula to look for the last but one cell? For example if value was in d19 how can I get it to look for d18? (All have values)

 

[awoohaw]

try:

      =INDEX(INDIRECT("'"&A2&"'!$E$2:$E$100"),MAX((ROW(INDIRECT("'"&A2&"'!$E$2:$E$100"))-1)*(ISNUMBER(INDIRECT("'"&A2&"'!$E$2:$E$100"))))-1)

As long as the column has no intervening blanks it should work.

 

[Gemandshed]

Thank you. Will it still work if the value is a date? Sorry for all the questions

 

[awoohaw]

yes, but you'll need to format the cell with the formula in it as a date so it displays properly. Experiment with it.

 

[Gemandshed]

Thank you so much. Really appreciate all your help.