PDA

View Full Version : Solved: Forumla with defined sheet name in other cell



danovkos
08-31-2009, 03:10 AM
hi all
pls, how can i do following?
i have a formula in cell B1. This is formula vlookup. In this vlookup i have name of sheet (to source table) „aktual“. I tried defined name of sheet in cell C1 and wrote it in formula.
It look like this:
the basic starting formula is:


=VLOOKUP(A1;'aktual'!$A$3:$D$10;4;0)

but i want to change source/sheet of source table in cell C1 (here will be written "aktual") and tried it with this:


=VLOOKUP(A1;'B1'!$A$3:$D$10;4;0)

But it of course doesnt Works.

Please how can i define this vlookup in B1, that the name of source sheet can be changed in C1 and if i change it here in C1 it will works and will be search in other sheet?

thx a lot.

mdmackillop
08-31-2009, 03:37 AM
You need to use INDIRECT (http://www.contextures.com/xlFunctions05.html#RefSheet)

=VLOOKUP(A1,INDIRECT("'" & C3 & "'!" & "A3: D10"),4,FALSE)
(omit space after colon)

danovkos
08-31-2009, 04:06 AM
yes, thank you
this is exactly what i needed :)
thank you very much