Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Reference
I have a workbook with 20 worksheets. The first worksheet has historical
data - for instance, Sheet1 Row 1 has Jan06,Feb06, etc. The rest of the rows have dollar amounts. All 19 other worksheets are reports and refer to the first worksheet. Rather than going manually through all 19 sheets to change the references each month (Manually changing Sheet19!A2 to Sheet19!B2), I would like to update the references by maybe adding a worksheet titled "ColumnRef" and typing B in A1 for February 2006. So I want Sheet 19 cell A1 to reference Sheet1!B2 by using "Sheet1" then ColumnRef!A1 (which would be B) then 2. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Reference
This is an interesting question. INDIRECT will allow you to piece together
an address or cell reference bit-by-bit: =INDIRECT("Sheet1!" & ColumnRef!A1 & 2) where cell A1 in sheet ColumnRef will contain A, B, etc. -- Gary''s Student "chrishutson123" wrote: I have a workbook with 20 worksheets. The first worksheet has historical data - for instance, Sheet1 Row 1 has Jan06,Feb06, etc. The rest of the rows have dollar amounts. All 19 other worksheets are reports and refer to the first worksheet. Rather than going manually through all 19 sheets to change the references each month (Manually changing Sheet19!A2 to Sheet19!B2), I would like to update the references by maybe adding a worksheet titled "ColumnRef" and typing B in A1 for February 2006. So I want Sheet 19 cell A1 to reference Sheet1!B2 by using "Sheet1" then ColumnRef!A1 (which would be B) then 2. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Reference
With B6 typed into cell A1,
=INDIRECT(A1) will return whatever value is in B6 Vaya con Dios, Chuck, CABGx3 "chrishutson123" wrote: I have a workbook with 20 worksheets. The first worksheet has historical data - for instance, Sheet1 Row 1 has Jan06,Feb06, etc. The rest of the rows have dollar amounts. All 19 other worksheets are reports and refer to the first worksheet. Rather than going manually through all 19 sheets to change the references each month (Manually changing Sheet19!A2 to Sheet19!B2), I would like to update the references by maybe adding a worksheet titled "ColumnRef" and typing B in A1 for February 2006. So I want Sheet 19 cell A1 to reference Sheet1!B2 by using "Sheet1" then ColumnRef!A1 (which would be B) then 2. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Reference
Thanks - you just saved me an immeasurable amount of time!
"Gary''s Student" wrote: This is an interesting question. INDIRECT will allow you to piece together an address or cell reference bit-by-bit: =INDIRECT("Sheet1!" & ColumnRef!A1 & 2) where cell A1 in sheet ColumnRef will contain A, B, etc. -- Gary''s Student "chrishutson123" wrote: I have a workbook with 20 worksheets. The first worksheet has historical data - for instance, Sheet1 Row 1 has Jan06,Feb06, etc. The rest of the rows have dollar amounts. All 19 other worksheets are reports and refer to the first worksheet. Rather than going manually through all 19 sheets to change the references each month (Manually changing Sheet19!A2 to Sheet19!B2), I would like to update the references by maybe adding a worksheet titled "ColumnRef" and typing B in A1 for February 2006. So I want Sheet 19 cell A1 to reference Sheet1!B2 by using "Sheet1" then ColumnRef!A1 (which would be B) then 2. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting a sheet name reference within a cell reference??? | Excel Discussion (Misc queries) | |||
Cell reference problem | Excel Worksheet Functions | |||
how to include a cell reference that is contained in a cell withi. | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Flexible Cell Reference | Excel Discussion (Misc queries) |