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

 

[SunnyAlv]

Hi @Gemandshed, Could you please attach an example of an excel image / table to make it easier for someone to help you?

 

[awoohaw]

Gemma,
How are you identifying each worksheet? Does the worksheet name match the name of the name on the summary sheet? (If not can you build a lookup table for this?)

 

[Gemandshed]

Hi. Yes it does.

Gemma,
How are you identifying each worksheet? Does the worksheet name match the name of the name on the summary sheet? (If not can you build a lookup table for this?)

 

[awoohaw]

try this:
Name1 sheet:

Book2
	A	B	C	D	E	F	G
1
2
3
4					4524
5
6
7
8
9
10
11					4255
12
13
14
15
16
Name1

Name2 Sheet:

Book2
	A	B	C	D	E	F	G
1					452
2
3
4
5
6
7					452
8
9
10
11
12
13
14
15
16
17
18
19					4254
20
21
Name2

Summary Sheet:
(note these formulas are VOLATILE and use up resources, if you have a very large workbook it will affect performance)
Also, formula must be committed to cell with CNTL-SHFT-ENTR keystroke (CSE).

Book2
	A	B	C	D	E
1	Name
2	Name1			4255
3	Name2			4254
4
5
6
Summary

Cell Formulas
Range	Formula
D2:D3	D2	=INDEX(INDIRECT(A2&"!$E$2:$E$100"),MAX((ROW(INDIRECT(A2&"!$E$2:$E$100"))-1)*(ISNUMBER(INDIRECT(A2&"!$E$2:$E$100")))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Gemandshed]

Thank you. I’ll give it a go

 

[awoohaw]

You're welcome. And Welcome to the Mr. Excel forum.

 

[Gemandshed]

Thank you. I’ve tried but it displays a #REF! Error
See below screenshots. There are numbers in Shirleys tab in column E. what am I doing wrong?

 

[awoohaw]

screen shots aren't visible. But, please look at the formulas for the MIDDLE INDIRECT function. It starts at row 2, and I subtract 1 after the function. If you start at row4, you need to subtract 3, etc.

To get a proof of how it works, paste all three miniworksheets I have above in a new workbook (rename sheets appropriately) and see if it works there. Then adjust to fit your worksheet setup.

 

[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