use a cell to reference a range in a vlookup
 

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)

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

"Dan" wrote:

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)

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.




In article ,
Dan wrote:

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)

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.

"JE McGimpsey" wrote:

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.




In article ,
Dan wrote:

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)

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


"Dan" wrote:

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.

"JE McGimpsey" wrote:

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.




In article ,
Dan wrote:

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)