Forum Discussion
Need a formula advances sheet name when copied
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 ...
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-c6d90033e188?ui=es-es&rs=es-es&ad=es#bm19
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.
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.