Using in Formula, Ref to a Sheet, a Cell Ref Cont the Sheet Name, Inst of Name Itself

Hi there,

Is anybody aware of an option to use e. g. in a vlookup a reference to a cell in the “source sheet” instead of the sheet name?

I have quite some sheets w/ similar data sets – which I due to various reasons cannot combine in one sheet.
E. g. in column A of the consolidation sheet the primary key is listed.

Not to search for each vlookup or index/match the correct sheet the primary key is belonging to, my idea was to do it one time for identification of the sheet. With one check in a consolidation sheet, I identify the “source sheet” of a data set, e. g. in column b.

The next columns are intended to perform vlookups based on the primary key in column A, but only based on the sheet which is stated in column B. Of course I can do an if formula w/ all the various tabs – but hoped that I can just reference column b for the sheet, as otherwise it is almost the same to perform if(iserror(vlookup(…),if(iserror(vlookup(…)… for each cell:

A2=<prim_key>
B2=<sheet_name> identified via a “one time” vlookup searching sheet by sheet the primaray key w/ iserror
C2=VLOOKUP(a2; '[?!?!?]'!A:W;MATCH("<column_header>";'[?!?!?]'!$1:$1;0);FALSE))

Where [?!?!?] should be replaced ideally by b2 – which is directly not possible …
Is there a known trick how to cheat XLSx to accept instead of the right sheet name the reference to a cell where the sheet name is stated?

Feedback highly welcome – as the if clauses make the formulas way harder to read, and slow down the performance when filtering.

Looking forward for creative ideas 
RoadRunner

Use INDIRECT, like this:

C2=VLOOKUP(a2; INDIRECT("'"&$B$2&"'!A:W");MATCH("<column_header>";INDIRECT("'"&$B$2&"'!$1:$1");0);FALSE)

Hope this helps.

Pete

Love it :-) !

Actually I tried arround w/ indirect but "mixed" the <"> and <'> incorrectly ...

Thanks a lot :-)