Use cell value as tab name in formula

[Sc0tt1e]

I need to make the Tina Walsh part of the below formula the value in C* so I can runthis formula down a column and use the tab names (which are the user names in column C) and have the formula find the relevant data on each of their respective tabs.

It's been so long since I've needed to do this that I can't remember (I think it has something to do with the Index function but I'm confusing myself using index and index).

I've tried googling it but I keep coming up with using named ranges which isn't what I want.

Any assistance would be appreciated (and I will kick myself for you when I see the simple answer to this issue.

Please Login or Register  to view this content.

[Andy Pope]

you need to use the INDIRECT function
assuming name is in C3

=INDEX(INDIRECT("'" & C3 & "'!E:E"),MATCH(9.99999999999999E+307,INDIRECT("'" & C3 & "'!E:E")))

[BoredWorker]

if you mean the in C2 have the name of "Tina Walsh" that exactly same with the tab name in other sheet and not other workbook?

If yes, replace 'Tina Walsh'!E:E with INDIRECT("'"&C2&"'!E:E)