Change reference "name" in formula to value inside other cell

[burgiz]

Hi guys,

Sorry for the not so flattering title of my issue, I simply do not know how to put it in short term.

My issue is:

I have an sheet with an overview.
It is like this in format:

1 2
A Doc. no. Amount (Reference to another sheet)
B Doc. no. Amount (Reference to another sheet)
C Doc. no. Amount (Reference to another sheet)

It is looking into another sheet to find the amount of a deal. I have been doing this manually - stupid, I know

All sheets are named a Doc. no. that matches the Doc. no. in the overview.

Now, my VLOOKUP forumla looks like this: =+VLOOKUP(G4;'50025041'!B24:D119;3;FALSE)
Working great - no problems.

BUT.. I would like to change the red number, so it points to A1 instead. Is it possible to do so in an easy way?

[Pete_UK]

You could do it using the INDIRECT function, like this in B1:

=VLOOKUP(G4;INDIRECT("'"&A1&"'!B24:D119");3;FALSE)

then copy down.

Hope this helps.

Pete

[burgiz]

I have never heard of the function before - works perfectly! Thank you very much

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.