having a table name be a variable
 

I am trying to make a look up formula that can be drag copied to other
cells, so as to make it easy for muliple users to use the spreadsheet

basically i have a main sheet that consolidates data from 52 other
sheets, each representing a week of the year.
Each sheet is named based on the week,
so 4.8.06
and 4.1.06

the main sheet has 52 additional columns named in the first row the
same as the sheet tab. so the row 1 for the column for 4.1.06 has the
same name as the sheet 4.1.06 where the information is pulled from.

i have a look up formula that checks an item and finds it on the sheet
=VLOOKUP(B6,'4.1.06'!$C$3:$M$221,11,FALSE)
This will be the same for every cell with the exception of the date
part , 4.1.06, i am looking for a way to make this so its easy to drag
copy

something like this, where z1 is where 4.1.06 is stored
=VLOOKUP(B6,Z$1!$C3:$M$221,11,FALSE)

i tried this and kept getting an error, any ideas?

Trav

in row 1


--
trav
------------------------------------------------------------------------
trav's Profile: http://www.excelforum.com/member.php...o&userid=31420
View this thread: http://www.excelforum.com/showthread...hreadid=531710

having a table name be a variable
 

If no spaces in your worksheet names
=VLOOKUP(B6,INDIRECT(Z$1&"!$C3:$M$221"),11,FALSE)

If spaces in your worksheet names
=VLOOKUP(B6,INDIRECT("'"&Z$1&"'!$C3:$M$221"),11,FA LSE)


"trav" wrote:


I am trying to make a look up formula that can be drag copied to other
cells, so as to make it easy for muliple users to use the spreadsheet

basically i have a main sheet that consolidates data from 52 other
sheets, each representing a week of the year.
Each sheet is named based on the week,
so 4.8.06
and 4.1.06

the main sheet has 52 additional columns named in the first row the
same as the sheet tab. so the row 1 for the column for 4.1.06 has the
same name as the sheet 4.1.06 where the information is pulled from.

i have a look up formula that checks an item and finds it on the sheet
=VLOOKUP(B6,'4.1.06'!$C$3:$M$221,11,FALSE)
This will be the same for every cell with the exception of the date
part , 4.1.06, i am looking for a way to make this so its easy to drag
copy

something like this, where z1 is where 4.1.06 is stored
=VLOOKUP(B6,Z$1!$C3:$M$221,11,FALSE)

i tried this and kept getting an error, any ideas?

Trav

in row 1


--
trav
------------------------------------------------------------------------
trav's Profile: http://www.excelforum.com/member.php...o&userid=31420
View this thread: http://www.excelforum.com/showthread...hreadid=531710