Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
frequency table for continuous variable | Excel Discussion (Misc queries) | |||
Linking table in Excel to word | Links and Linking in Excel | |||
Pivot Tables, Help? | Excel Discussion (Misc queries) | |||
How do I create a one variable data table? | Excel Discussion (Misc queries) |