Forum Discussion
Change one character within a formula
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
3 Replies
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.
- So you mean that I cannot use indirect in my case ?
because i have many attendance workbooks (attendance y1 to attendance y13) and i cannot leave them always open.In that case, INDIRECT is not a good solution. Perhaps someone else will suggest a better method.
