Cell reference in different worksheet in formula

[Robb]

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.

 

[Marcelo]

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: