software

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
quot;ColumnRefquot; and typing B in A1 for February 2006. So I want Sheet 19 cell A1
to reference Sheet1!B2 by using quot;Sheet1quot; then ColumnRef!A1 (which would be B)
then 2.

This is an interesting question. INDIRECT will allow you to piece together
an address or cell reference bit-by-bit:

=INDIRECT(quot;Sheet1!quot; amp; ColumnRef!A1 amp; 2)

where cell A1 in sheet ColumnRef will contain A, B, etc.

--
Gary''s Studentquot;chrishutson123quot; wrote:

gt; I have a workbook with 20 worksheets. The first worksheet has historical
gt; data - for instance, Sheet1 Row 1 has Jan06,Feb06, etc. The rest of the rows
gt; have dollar amounts. All 19 other worksheets are reports and refer to the
gt; first worksheet. Rather than going manually through all 19 sheets to change
gt; the references each month (Manually changing Sheet19!A2 to Sheet19!B2), I
gt; would like to update the references by maybe adding a worksheet titled
gt; quot;ColumnRefquot; and typing B in A1 for February 2006. So I want Sheet 19 cell A1
gt; to reference Sheet1!B2 by using quot;Sheet1quot; then ColumnRef!A1 (which would be B)
gt; then 2.

With B6 typed into cell A1,
=INDIRECT(A1) will return whatever value is in B6

Vaya con Dios,
Chuck, CABGx3
quot;chrishutson123quot; wrote:

gt; I have a workbook with 20 worksheets. The first worksheet has historical
gt; data - for instance, Sheet1 Row 1 has Jan06,Feb06, etc. The rest of the rows
gt; have dollar amounts. All 19 other worksheets are reports and refer to the
gt; first worksheet. Rather than going manually through all 19 sheets to change
gt; the references each month (Manually changing Sheet19!A2 to Sheet19!B2), I
gt; would like to update the references by maybe adding a worksheet titled
gt; quot;ColumnRefquot; and typing B in A1 for February 2006. So I want Sheet 19 cell A1
gt; to reference Sheet1!B2 by using quot;Sheet1quot; then ColumnRef!A1 (which would be B)
gt; then 2.

Thanks - you just saved me an immeasurable amount of time!

quot;Gary''s Studentquot; wrote:

gt; This is an interesting question. INDIRECT will allow you to piece together
gt; an address or cell reference bit-by-bit:
gt;
gt; =INDIRECT(quot;Sheet1!quot; amp; ColumnRef!A1 amp; 2)
gt;
gt; where cell A1 in sheet ColumnRef will contain A, B, etc.
gt;
gt; --
gt; Gary''s Student
gt;
gt;
gt; quot;chrishutson123quot; wrote:
gt;
gt; gt; I have a workbook with 20 worksheets. The first worksheet has historical
gt; gt; data - for instance, Sheet1 Row 1 has Jan06,Feb06, etc. The rest of the rows
gt; gt; have dollar amounts. All 19 other worksheets are reports and refer to the
gt; gt; first worksheet. Rather than going manually through all 19 sheets to change
gt; gt; the references each month (Manually changing Sheet19!A2 to Sheet19!B2), I
gt; gt; would like to update the references by maybe adding a worksheet titled
gt; gt; quot;ColumnRefquot; and typing B in A1 for February 2006. So I want Sheet 19 cell A1
gt; gt; to reference Sheet1!B2 by using quot;Sheet1quot; then ColumnRef!A1 (which would be B)
gt; gt; then 2.