Vlookup to change worksheet reference

**ChrisHowk** · 09-07-2012, 11:57 AM

I am trying to do a Vlookup across 17 different worksheets and it is getting old manually changing the worksheet reference so I was hoping there was a work around. This is the function that I currently have:

=VLOOKUP(A60,Week1!$A$1:$H$16,5)

I would for "week1" to change to "week2","week3" and so on as I copy the formula across. The table reference stays the same on the the sheets as well as the lookup value.

If it helps, the table I am trying to copy the formula across in has the same headings as the worksheets.

Any help would be appreciated.

**JosephP** · 09-07-2012, 12:04 PM

if the headers are in B1 C1 D1 and so on and the formula goes in B2
=VLOOKUP($A60,INDIRECT("'"&B$1&"'!$A$1:$H$16"),5)
then copy across

**ChrisHowk** · 09-07-2012, 12:30 PM

The headers would be C20,D20,.... and the formula would go in multiple rows below from 21 through 65. I tried inserting C20 in place of B1 but got an error.

**JosephP** · 09-07-2012, 02:00 PM

what error? did you use C$20 in place of B$1? may help to provide a workbook

**ChrisHowk** · 09-07-2012, 02:37 PM

It was the basic error message saying something was wrong with the formula. It took me the [!] part.

Fantasy Football.xlsx

**ChrisHowk** · 09-07-2012, 02:42 PM

Sorry. I think I figured out what I did wrong. I didn't catch that I was including the table reference outside the INDIRECT funciton. Thank you for your help! it worked just fine when I was able to get over my "user error".