Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using cell value to reference external worksheet? | Excel Worksheet Functions | |||
Copy worksheet & maintain cell reference across worksheets | Excel Worksheet Functions | |||
copied formula has correct cell reference, but result of original | Excel Worksheet Functions | |||
Indirect reference from one worksheet to another | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |