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]

=SUM(INDIRECT(YEAR(TODAY())&"!B4:B8"))

But be aware that INDIRECT is a volatile function so if used a lot can impact the sheet's performance.

 

[JOEE1979]

=SUM(INDIRECT(YEAR(TODAY())&"!B4:B8"))

But be aware that INDIRECT is a volatile function so if used a lot can impact the sheet's performance.

One of the formulas I'm using is

=IF(SUMIFS((INDIRECT(YEAR(TODAY())&"!B:B")),(INDIRECT(YEAR(TODAY())&"!G:G")),(CONCAT((TEXT(C$4,"DD-")),(TEXT($C$2,"MMM-YY")))),(INDIRECT(YEAR(TODAY())&"!N:N")),$K5)=0,"",SUMIFS((INDIRECT(YEAR(TODAY())&"!B:B")),(INDIRECT(YEAR(TODAY())&"!G:G")),(CONCAT((TEXT(C$4,"DD-")),(TEXT($C$2,"MMM-YY")))),(INDIRECT(YEAR(TODAY())&"!N:N")),$K5))

Problem is I have it about 800 times in a worksheet
Is is going to slow down the worksheet?

 

[Peter_SSs]

Problem is I have it about 800 times in a worksheet
Is is going to slow down the worksheet?

Probably not, but it partially depends on your system resources. Best way to find out is to try it.

 

[Alex Blakenburg]

One of the formulas I'm using is

=IF(SUMIFS((INDIRECT(YEAR(TODAY())&"!B:B")),(INDIRECT(YEAR(TODAY())&"!G:G")),(CONCAT((TEXT(C$4,"DD-")),(TEXT($C$2,"MMM-YY")))),(INDIRECT(YEAR(TODAY())&"!N:N")),$K5)=0,"",SUMIFS((INDIRECT(YEAR(TODAY())&"!B:B")),(INDIRECT(YEAR(TODAY())&"!G:G")),(CONCAT((TEXT(C$4,"DD-")),(TEXT($C$2,"MMM-YY")))),(INDIRECT(YEAR(TODAY())&"!N:N")),$K5))

Problem is I have it about 800 times in a worksheet
Is is going to slow down the worksheet?

Are you sure you want to do this ?
Typically you work on data in arrears. By putting Today() in your formulas on 1 Jan or whatever working day you are back at work everything is going to have clicked over to the new year.

 

[JOEE1979]

=SUM(INDIRECT(YEAR(TODAY())&"!B4:B8"))

But be aware that INDIRECT is a volatile function so if used a lot can impact the sheet's performance.

Will this formula turn over in the next year? or will I have to update it?

 

[Peter_SSs]

Will this formula turn over in the next year? or will I have to update it?

You will not have to update the formula. As soon as you open the workbook next year it will be looking for a sheet called '2025'.

 

[JOEE1979]

Ok thank you so much. It scared me for a second, I simulated the date for next year and it didnt pick up the info.

 

[Peter_SSs]

I simulated the date for next year and it didnt pick up the info.

How did you do that?

 

[JOEE1979]

I manipulated the date on this

Project tracker (1)1111.xlsx
	W	X	Y	Z	AA
3
4		Time Entry
5
6
7
8			Year	2024
9
10			Month	3
11
12			Day 1	1
13
14			Date	2024-03-31
15
Current Month

Cell Formulas
Range	Formula
Z8	Z8	=YEAR(Z14)
Z10	Z10	=MONTH(Z14)
Z14	Z14	=TODAY()