INDIRECT VLOOKUP formula problem

[danovkos]

Hi all,
pls. i have 12 sheets. Names of sheets are 1, 2, 3....
I use this formula in sheet 2, that it will look to sheet 1 and return value, but it return 0.
Pls. where is the problem?
thx for any help


=VLOOKUP(A4;INDIRECT("'" & (RIGHT(CELL("filename");1)-1) & "'!" & $A$4:$AB$2500);26;0)

[Bob Phillips]

Why are you using CELL("filename")?

Why not just =VLOOKUP(A4,'1'!$A$4:$AB$2500,26,0)

[danovkos]

because i dont want to always overwrite my formula.

The sheets are month of year. And i create it alway from previous month.
Now i have month 1 and 2. The 3 create folowing. I make copy of 2 And i want, that formula know, that now is it other name of sheet.
Simply i have to use this part of formula to figured out, whats is name of sheet, where actual is formula .
I don know if i am clear.

[Bob Phillips]

Try this

=VLOOKUP(A4,INDIRECT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99)-1&"!A4:AB2500"),26,FALSE)

[danovkos]

this forumla return
#VALUE!
of course i had to correct it for my region ";" instead ","


=VLOOKUP(A4;INDIRECT(MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;99)-1&"!A4:AB2500");26;FALSE)

what can be wrong?