Cell reference in different worksheet in formula
 

I'm working on a Daily report spreadsheet. Each page is a different day, so
it's a 365 (366) page spreadsheet plus a Year End totals page. I have 16
formulas that reference cells from the prior day. Now, my issue is that if I
copy and paste the formula on another day, it still references the page
originally referenced.

Ex: The YTD field has the following formula: SUM(E19,E20,'May-22'!E20)

This adds the two cells from the current page (which would be the May-23
page) to the one cell from the prior page. Now if I want the same format of
the formula on the May-24 page, and I copy/paste the formula, I then have to
manually change the reference from May-22, to May-23. Across a 365 page
report, 16 formulas are a lot of manual labor.

Now I seem to recall there is a way to reference the prior page, without
actually naming it. That way, no matter where you paste it, it will refer to
the page before. Unfortunately, I can't remember the tag for that.

Any help you can provide would be greatly appreciated.

Cell reference in different worksheet in formula
 

Robb,

type the name of the prior worksheet in one cell of each spreadsheet, like
on AA1 for eg.

than to refers to this name in a formula you can use Indirec and Address
toghether

eg.
on May-23 do you have on AA1 May-22

SUM(E19,E20,indirect(address(20,5,1,1,aa1)))
Than you copy the formulas for all of spreadsheets

please let me know if it helps
regards from Brazil
Marcelo






"Robb" escreveu:

I'm working on a Daily report spreadsheet. Each page is a different day, so
it's a 365 (366) page spreadsheet plus a Year End totals page. I have 16
formulas that reference cells from the prior day. Now, my issue is that if I
copy and paste the formula on another day, it still references the page
originally referenced.

Ex: The YTD field has the following formula: SUM(E19,E20,'May-22'!E20)

This adds the two cells from the current page (which would be the May-23
page) to the one cell from the prior page. Now if I want the same format of
the formula on the May-24 page, and I copy/paste the formula, I then have to
manually change the reference from May-22, to May-23. Across a 365 page
report, 16 formulas are a lot of manual labor.

Now I seem to recall there is a way to reference the prior page, without
actually naming it. That way, no matter where you paste it, it will refer to
the page before. Unfortunately, I can't remember the tag for that.

Any help you can provide would be greatly appreciated.