Vlookup + Address functions combined

[Peter]

Dear Exellions,

I am using the following formula to look for a date in current "Summary"
sheet, then see if it can find relevent match in "ARBSCAPES" sheet and
return a numerical value:

=VLOOKUP(L$1,ARBSCAPES!$A$13:$I$126,9,FALSE)

I need this formula to include something like the ADDRESS function to avoid
having to type in an alternative sheet reference for the other 80 odd sheets
in same Excel file.

I, currently have the 80 odd sheets listed in column A of my "Summary"
sheet, and would like the VLOOKUP to look something like this:

=VLOOKUP(L$1,Address(A2)!$A$1:$B$126,2,FALSE), but this will not work.

Eg, Summary sheet
Row Col A Col B Col L1 Col M
1 01/12/05
01/11/05
2 Beens
=VLOOKUP(L$1,Address(A2)!$A$1:$B$12,2,FALSE)
3 Shoots
=VLOOKUP(L$1,Address(A3)!$A$1:$B$12,2,FALSE)
4 Carrots
=VLOOKUP(L$1,Address(A4)!$A$1:$B$12,2,FALSE)

Eg, Beens sheet
Row Col A Col B
1 01/12/05 £5.25
2 01/11/05 £7.50
3 01/10/05 £2.25

INDIRECT formula is not suitable in this scenario.

Thanks.
Peter

 

[tony h]

try the indirect function

 

[tony h]

try
=VLOOKUP(L$1, Indirect(A2 & "!$A$1:$B$12",true) ,2,FALSE)