Using in a Formula, Referring to Another Sheet, a Cell Reference Containing the Sheet Name, Instead of the Sheet Name Itself

[1971erRoadRunner]

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=

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</column_header></sheet_name></prim_key>

 

[circledchicken]

Hi and welcome to the forum,

You can try using the INDIRECT function, see here for example:
http://www.contextures.com/xlFunctions05.html#RefSheet

Although note that the function is volatile (Volatile Excel Functions -Decision Models) and so should be used sparingly.

 

[1971erRoadRunner]

Hi CircleChicken,
Thanks a lot for the fast hint and warnings!
Actually for me I assume it is still reasonable as having in 5+ columns to check primary key in 10+ tabs the if-statements are very long and it is easy to make mistakes.
Let's see how the performance is when all calculations are in place - so far I have not faced any obvious performance issues.
RoadRunner