Vlookup copied across to look at different sheets???

**turbofatty** · 11-09-2011, 11:48 AM

Hi All,

Hopefully this is just a quick one but a user has just asked me a question that i'm not sure of the solution to?

He wants to do a Vlookup which works, not a problem.

But he would like to copy the formula across the sheet, again not a problem, but every time it goes into the next column across he wants it to look at a different sheet??

There is similar, but different, data on all of his 96 sheets, so when its copied across on his front master sheet, the vlookup looks at each different sheet. (And yes, he know he will have a master sheet that is at least 96 columns wide, as it will have 96, similar, but different vlookups in it!!)

The first vlookup will look something like this (his sheets are just called 1,2,3 etc):

=VLOOKUP($A$3,'1'!$A$3:$E$30,2,FALSE)

The 2nd one in the column next to it will look like:

=VLOOKUP($A$3,'2'!$A$3:$E$30,2,FALSE)

He just doesn't want to have to change 96 vlookups manually!!

I hope all that made sense....

Thanks again all you genius' out there!!

**NBVC** · 11-09-2011, 11:50 AM

Try:

=VLOOKUP($A$3,INDIRECT("'"&ROWS($A$3:$A3)&"'!$A$3:$E$30"),2,FALSE)

copied down.

**turbofatty** · 11-09-2011, 12:00 PM

Hi NBVC,

Thanks for your swift response but it didn;t work, or i have misunderstood something...

I have attached a workbook now to probably explain the problem better.

I have altered the vlookup MANUALLY in each one (Row 3 on Sheet1) to look at a different sheet.

We just don't want to have to do this 96 times!!

Thanks again...

**NBVC** · 11-09-2011, 12:02 PM

I had assumed you were copying downward, but if you are copying across columns, then try:

=VLOOKUP($A3,INDIRECT("'"&COLUMNS($A$1:A$1)&"'!$A$3:$E$30"),2,FALSE)

copied across and then copied down for the rest of the items in column A....

**turbofatty** · 11-09-2011, 12:10 PM

Hi NBVC,

I have no real idea what this means

but , hell yeah, it works!!!

You are a bona-fide genius and don't let anyone tell you any different!!

Cheers Ears

**NBVC** · 11-09-2011, 12:15 PM

FYI,

COLUMNS() counts number of columns in the range...

so COLUMNS($A$1:A$1) is 1. As you copy across, it becomes COLUMNS($A$1:B$1) which means 2, and so on. The $ are anchors, so that we always start at A1 and only the column letter changes as you copy across.. This is how we get the sheetnames that you have conveniently named numerically...

But in order to reference sheetnames "indirectly" like that, we need to use the INDIRECT function and therefore INDIRECT("'"&COLUMNS($A$1:A$1)&"'!$A$3:$E$30") indirectly references the range defined by the sheetname gotten from COLUMNS() function....