use a cell to reference a range in a vlookup

[Guest]

I have a spreadsheet that has the same line on it, and would like to automate
a vlookup so that i do not have to change the tab name. I have the tab names
in a column already, before that cell I was making the range with a formula
so that I coudl reference that cell to define my range. That does not seem
to be working, does anyone know how to work around that? Example below

Formula that works
=VLOOKUP("a",Sheet2!1:65536,2,FALSE)

Formula that does not work
A1 = Sheet2!1:65536
=VLOOKUP("a",A1,2,FALSE)

 

[Guest]

try
=VLOOKUP("a",indirect(A1),2,FALSE)

 

[JE McGimpsey]

You could use

=VLOOKUP("a",INDIRECT(A1),2,FALSE)

but I wonder why you're using another cell to contain range references.
There are probably better ways to identify the ranges you want.

 

[Guest]

I have 60 sheets, all in the same format. I want to make a summary sheet for
the same line on all the sheets. So I want to use a vlookup to give me the
specific row that i want, and I wanted the cell to give me the sheet name.

The indirect did not work, because they are on different sheets I think.

 

[Guest]

try changing A1 to
=Sheet2!A1
and use
=VLOOKUP("a",offset(indirect(A1),0,0,65636,2),2,FALSE)