Change one character within a formula

Copper Contributor

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.

 

nnn.png

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

@chamseddine_hamdeni 

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.

@chamseddine_hamdeni 

In that case, INDIRECT is not a good solution. Perhaps someone else will suggest a better method.