Cell Reference

[chrishutson123]

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.

[Gary''s Student]

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.

[CLR]

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.

[chrishutson123]

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.