worksheet referencing

[JOEE1979]

I have a formula that changes based on the year.
Every year I have to go into my worksheets and change all my formulas to reflect my current year because I open a new worksheet.

A formula example is
=SUM('2024'!B4:B8)

Is it possible to have a formula for the '2024' to reflect the current year so I don't have to change so many formulas?

Thank you

 

[Peter_SSs]

I manipulated the date on this

OK, but the formula that I gave you does not use those cells.
If you want to use that as the basis then use the formula below which would be just as good or better

=SUM(INDIRECT(Z8&"!B4:B8"))

 

[JOEE1979]

OK, but the formula that I gave you does not use those cells.
If you want to use that as the basis then use the formula below which would be just as good or better

=SUM(INDIRECT(Z8&"!B4:B8"))

It would take too long to replace the formulas on the page, im gonna leave it the way it is,, thank you for your help

 

[JOEE1979]

actually I did a find and replace function, replaced "YEAR(TODAY())" with "Z8".... easier than I thought,,, 5000 replacements,,, lol

Thanks

 

[Peter_SSs]

Good news. Hope it all works for you!

 

[RobOrBob]

I'm late to the party a usual but making some assumptions:
If the SUMs only apply to the current year, why not just call the current year worksheet 'This Year' and use this as the qualifier?
When a new year rolls over just rename the expired year as the year it represents e.g. 2023 (after the new worksheet 'This Year' has been created).

 

[Peter_SSs]

why not just call the current year worksheet 'This Year' and use this as the qualifier?
When a new year rolls over just rename the expired year as the year it represents e.g. 2023 (after the new worksheet 'This Year' has been created).

You cannot have two worksheets with the same name so you cannot create the new worksheet 'This Year' until the previous 'This Year' worksheet has been renamed to '2023' At that point all the formulas that were pointing at 'This Year' will automatically change to point at the renamed '2013' worksheet. That will not change when you then create the new sheet and name it 'This Year'

 

[RobOrBob]

You cannot have two worksheets with the same name so you cannot create the new worksheet 'This Year' until the previous 'This Year' worksheet has been renamed to '2023' At that point all the formulas that were pointing at 'This Year' will automatically change to point at the renamed '2013' worksheet. That will not change when you then create the new sheet and name it 'This Year'

Fair point!
But if you copy 'ThisYear' as that year expires then rename the copy as '2023', the name manager items will still exist and qualified as 'ThisYear' for the 'ThisYear' sheet. Excel will also create the same named items qualified by the copied worksheet name eg ' Thisyear (2)' and renaming the copied worksheet will also give you the sums for '2023'.
Obviously 'ThisYear' worksheet will need to be cleared of the 2023 data.

 

[RobOrBob]

Forgot to add. I'm assuming that the SUMs are used on a seperate worksheet.
The new '2023' named items will have a scope of that worksheet whereas the 'ThisYear' are scoped at the workbook level.