Create variable within a tab

[franklinsam]

I am using Office 365 on Windows 11, all updates are installed.


My spreadsheet consists of multiple tabs, each tab has a name, the name is a number that signifies the year (e.g. 2021, 2022, etc.). I want to create a variable that used the tab name. So, I use the formula:
=RIGHT(CELL("filename"),4)
Next I create a variable in the Name Manager, and everything works fine in the sheet that I'm working in, call it 2023. When I go to work in tab 2024 I still get the date from 2023 so I do a Calculate Now and it updates fine however if I go back to tab 2023 it now shows the date as 2024.

While I could create a variable in Name Manager for the tab name and define the variable to the scope of the tab. Is there a better way?

 

[BigBeachBananas]

What are you trying to accomplish with this variable?

 

[franklinsam]

I have a running total of values, each has a date associated with the value, the table name is Withdraws

from this is I create an overall total and a year to date total. The YTD date formula looks like this
=SUMPRODUCT(--(YEAR(Withdraws[Date])=INT(CurrentYear)),Withdraws[Amount])

 

[Fluff]

You need to include a cell reference like

=RIGHT(CELL("filename",A1),4)

 

[franklinsam]

You need to include a cell reference like

=RIGHT(CELL("filename",A1),4)

This works but I had to make sure that the reference cell was a blank cell in the current tab

 

[franklinsam]

Thank you very much

 

[Fluff]

Glad we could help & thanks for the feedback.