I need to get the sheet name from a cell.
On image below I'm trying to get '4/2'!$AB60.
- need to get the date which is 4/2 (cell D5)
- combine it with !$AB60
- now the cell D6 will have a formula value of '4/2'!$AB60
- it will display the value.
Is there a way to formulate this instead of what I currently be doing, manually updating the each column dates (from C6 to AF6)? Is this even possible?
CodePudding user response：
Formula from the screenshot: =JOIN(,LEFT(D5,LEN(D5),!$AB60)
The problem with this formula is !$AB60 as it is not a valid value, instead use "!$AB60" or use =TO_TEXT(D5)&"!$AB60". To use the result of this formula as a cell reference use INDIRECT, i.e.:
=INDIRECT(TO_TEXT(D5)&"!$AB60")