Need a formula advances sheet name when copied

[DumDeeDah]

I want to carry forward 1 figure on a sheet to the next fortnight sheet. When I copy the sheet to create the next sheet it keeps the same refernce link.

I use ='2022 2 2'!L33

This brings in the figure from previous sheet. But then each time sheet is copied it will only obtain the total from the sheet '2022 2 2'

How can I have it add on to the next sheet to be '2022 2 16' etc

I hope this makes sense.

Thanks

[#2]

@DumDeeDah 

Hello:
Excel treats sheet names as aliases, that is, sheet names start from Sheet1 to Sheet#? and not like the name displayed in the tab.
The formula to use would be (in your case):
'=ADDRESS(ROW(L33); COLUMN(L33);4;1;" Sheet"&SHEET(L33))'
being:
'SHEET()' gives you the sheet number in which the selected cell is located.
'COLUMN()' gives you the column number of the selected cell.
'ROW()' gives you the row number of the selected cell.
and
'ADDRESS()': creates the address for the cell
See help functions in:
https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d9...

[#3]

Thanks for responding.

When I add this formula in I know get a #VALUE! message in the cell below not being able to use this data.

Plus now the colomns in L wont total on any spreadsheet.

Any other suggestions.

Thanks so much.

[#4]

Give an example of using your formula.
To see if I can help you.