I need to create a reference in a formula that points to a cell on a sheet in
same workbook. I know I can point and click it to show ='Jan-01'!G16, but I
have to recreate it for every day of the year and in 16 places in each day.
the pages are all exactly the same, except for the tab name. I don't want to
have to keep writing the fomulas over 5800 times. I know there is a way to
create a generic reference so that I can just copy and paste the sheets and
not have to keep adjusting the formulas. Please help. Thank you...

Try INDIRECT:

Say you have a column A1:A1 with values "Jan-01" thru "Dec-01"

You can use =INDIRECT("'"&A1&"'!G16")

HTH
--
AP

"Robb" a écrit dans le message de news:
...
I need to create a reference in a formula that points to a cell on a sheet
in
same workbook. I know I can point and click it to show ='Jan-01'!G16, but
I
have to recreate it for every day of the year and in 16 places in each
day.
the pages are all exactly the same, except for the tab name. I don't want
to
have to keep writing the fomulas over 5800 times. I know there is a way
to
create a generic reference so that I can just copy and paste the sheets
and
not have to keep adjusting the formulas. Please help. Thank you...

AP

Thanks. I actually need to have it reference a cell on a different sheet.
For instance, in cell E37 for sheet Jan-02, I need to do the following:

=SUM(E36,'Jan-01'!E37)

....to get the YTD $$ Amt. I then have to do it for Jan-03, Jan-04, etc...

Instead of typing the formulas over, or adjusing them if I copy a sheet, I
wanted to use a generic reference that points from the Jan-02 page to the
Jan-01 page , and so on, keeping in mind that each sheet represents on day
and each day has 16 similar calculations...

Any ideas????


"Ardus Petus" wrote:

Try INDIRECT:

Say you have a column A1:A1 with values "Jan-01" thru "Dec-01"

You can use =INDIRECT("'"&A1&"'!G16")

HTH
--
AP

"Robb" a écrit dans le message de news:
...
I need to create a reference in a formula that points to a cell on a sheet
in
same workbook. I know I can point and click it to show ='Jan-01'!G16, but
I
have to recreate it for every day of the year and in 16 places in each
day.
the pages are all exactly the same, except for the tab name. I don't want
to
have to keep writing the fomulas over 5800 times. I know there is a way
to
create a generic reference so that I can just copy and paste the sheets
and
not have to keep adjusting the formulas. Please help. Thank you...