I am trying to re-compile a whole lot of data which is displayed over 30
worksheets(numbered 1-30). I want to perform the same vlookup function for
several different parts of the data for all of these spreadsheets to compile
all of the data quickly on the one new worksheet. Is there any way that I can
automatically enter the worksheet name in the vlookup function rather than
having to manually enter the worksheet name for every column?

The formula looks like this: =VLOOKUP($A4,'01'!$A$1:$L$60,$C$2,FALSE) where
'01' is the name of worksheet 01. The next column has the formula:
=VLOOKUP($A4,'02'!$A$1:$K$60,$C$2,FALSE) and so on.

As I have to do this with many worksheets some help would be much
appreciated.

Create a list of sheet names
example:
A1: 01
A2: 02
A3: 03
and so on....

B1 =VLOOKUP($A4,INDIRECT("'"&A1&"'!$A$1:$L$60"),$C$2, FALSE)

Copy all the way down as far as needed


"Leo" wrote:

I am trying to re-compile a whole lot of data which is displayed over 30
worksheets(numbered 1-30). I want to perform the same vlookup function for
several different parts of the data for all of these spreadsheets to compile
all of the data quickly on the one new worksheet. Is there any way that I can
automatically enter the worksheet name in the vlookup function rather than
having to manually enter the worksheet name for every column?

The formula looks like this: =VLOOKUP($A4,'01'!$A$1:$L$60,$C$2,FALSE) where
'01' is the name of worksheet 01. The next column has the formula:
=VLOOKUP($A4,'02'!$A$1:$K$60,$C$2,FALSE) and so on.

As I have to do this with many worksheets some help would be much
appreciated.