Forum Discussion
Solved
Rename Cell to Various Different Sheet Titles
Hi, I am trying to create a formula that allows me to have cells equal to the names of different sheets. I have read several interesting posts that created solutions for having a cell automatical...
You can achieve it without VBA coding. Try the following in name manager and call that name to any cell. See the attached file.
Important Note: You must enable Excel 4.0 Macros from macro settings.
=TEXTAFTER(GET.WORKBOOK(1)&T(NOW()),"]")
So my approach would be to reserve a cell on each sheet for the sheet name. For example cell A1 could be:
=LET(path,CELL("filename",A1),RIGHT(path,LEN(path)-SEARCH("]",path)))
Then make sure you have all the sheets 'bound' by a known sheet name. For example lets say you always have JAN first and the last sheet is SUMMARY and you insert months inbetween as the year goes by then:
=DROP(HSTACK('JAN:SUMMARY'$A$1),,-1)
where that will 'stack' all the sheet names (each found in cell A1 of each sheet) and then drop that last one from this SUMMARY sheet.