VLOOKUP table on one sheet that references every sheet in workbook

**sportsman56** · 04-12-2013, 04:22 PM

I have a workbook that has about 60 sheets that all have the same format.

I create a "Master" sheet where I have a row of dates in column A. In column B I want a Vlookup function like this =vlookup($A1,Sheet2!$D$2:$K$140,8,TRUE), In column C a vlookup function like this: ==vlookup($A1,Sheet3!$D$2:$K$140,8,TRUE) and so on for the remaining sheets in the workbook. So after the macro is finished, my master sheet should have about 60 columns.

I'd rather not have to manually change each sheet name in my vlookup columns. Can anyone think of a macro that could help me out with this?

**TMS** · 04-12-2013, 05:05 PM

Maybe try:

=VLOOKUP($A1,INDIRECT("Sheet" & COLUMN(B$1) & "!$D$2:$K$140"),8,TRUE)

and drag across.

Regards, TMS

**TMS** · 04-13-2013, 07:54 PM

Is this resolved?

**sportsman56** · 04-15-2013, 12:19 PM

Thanks for the quick response,

Say I have three sheets.

The first sheet (Master Sheet) is the sheet where I want my vlookup table
The second and third sheets are called (WTI) and (Brent).

How would I change the vlookup formula so that I can just simply drag across? I tried your formula and get #N/A in the cells.

**FDibbins** · 04-15-2013, 12:22 PM

In Trevor's formula...

=VLOOKUP($A1,INDIRECT("Sheet" & COLUMN(B$1) & "!$D$2:$K$140"),8,TRUE)
the "sheet" is for referencing your sheet name. So change that to the cell ref for where you have your sheet names stored