Sep 02 2021 02:58 AM - edited Sep 02 2021 02:58 AM
Hello, I have a question in EXCEL
I have a formula and I want to change only one character within this formula to be referenced in another cell. I mean when I change the cell value with a new character, the formula will take the new caracter instead of the old one.
My case is the following:
I have a formula in cell C152. In this formula, the value 3 will be changed according to the value of the cell E148.
Example: the formula now is ='[Attendance Y3.xlsx]Attendance register'!$A$9:$A$29
this is because the cell 148 contains the value 3. If I change it to 4, the formula must be
='[Attendance Y4.xlsx]Attendance register'!$A$9:$A$29
Thank you for your help
Sep 02 2021 04:08 AM
You can use INDIRECT for this:
=INDIRECT("'[Attendance Y" & C152 & ".xlsx]Attendance register'!$A$9:$A$29")
Warning: this will only work if the other workbook is open. INDIRECT cannot work with references to a closed workbook.
Sep 02 2021 05:42 AM
Sep 02 2021 05:52 AM
In that case, INDIRECT is not a good solution. Perhaps someone else will suggest a better method.