Referencing a different tab according to a formula result
 

Hi Team,
I have multiple tabs each with similar but different 2X2 reference data on
them.
On my user input sheet I want the user to input data that then calculates
which tab the rest of the formula should perform the index/vlookup/hlookup
on. I know about =&[Tab] function in the header/footer but can I do a
=[$C$2]! where $C$2 changes?
Thanks
Ditch

Referencing a different tab according to a formula result
 

Need more info.

If cell C2 contains a sheet name then to use that in a VLOOKUP formula:

=VLOOKUP(A1,INDIRECT("'"&C2&"'!A:B"),2,0)

This is effectively:

=VLOOKUP(A1,Sheet_name!A:B,2,0)


--
Biff
Microsoft Excel MVP


"Ditch" wrote in message
...
Hi Team,
I have multiple tabs each with similar but different 2X2 reference data on
them.
On my user input sheet I want the user to input data that then calculates
which tab the rest of the formula should perform the index/vlookup/hlookup
on. I know about =&[Tab] function in the header/footer but can I do a
=[$C$2]! where $C$2 changes?
Thanks
Ditch

Referencing a different tab according to a formula result
 

You could use INDIRECT

Eg if you have this in C3:
=VLOOKUP(A2,Sheet3!A:B,2,0)

You could use INDIRECT to make the formula flexible
for the sheetname, viz.:
=VLOOKUP(A3,INDIRECT("'"&C$2&"'!A:B"),2,0)
where C2 would contain the required sheetname, ie: Sheet3, Sheet2, etc
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Ditch" wrote:
Hi Team,
I have multiple tabs each with similar but different 2X2 reference data on
them.
On my user input sheet I want the user to input data that then calculates
which tab the rest of the formula should perform the index/vlookup/hlookup
on. I know about =&[Tab] function in the header/footer but can I do a
=[$C$2]! where $C$2 changes?
Thanks
Ditch