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

 

[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.

 

[awoohaw]

my pleasure. Just remember if your workbook gets large your performance will degrade. There are ways to ameliorate that as well.

Best Wishes!