Change one character within a formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2710955%22%20slang%3D%22en-US%22%3EChange%20one%20character%20within%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2710955%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20have%20a%20question%20in%20EXCEL%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20formula%20and%20I%20want%20to%20change%20only%20one%20character%20within%20this%20formula%20to%20be%20referenced%20in%20another%20cell.%20I%20mean%20when%20I%20change%20the%20cell%20value%20with%20a%20new%20character%2C%20the%20formula%20will%20take%20the%20new%20caracter%20instead%20of%20the%20old%20one.%3C%2FP%3E%3CP%3EMy%20case%20is%20the%20following%3A%3C%2FP%3E%3CP%3EI%20have%20a%20formula%20in%20cell%20C152.%20In%20this%20formula%2C%20the%20value%203%20will%20be%20changed%20according%20to%20the%20value%20of%20the%20cell%20E148.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22nnn.png%22%20style%3D%22width%3A%20842px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F307591iC902C868C2C52A26%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22nnn.png%22%20alt%3D%22nnn.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EExample%3A%20the%20formula%20now%20is%26nbsp%3B%3D'%5BAttendance%20Y3.xlsx%5DAttendance%20register'!%24A%249%3A%24A%2429%3C%2FP%3E%3CP%3Ethis%20is%20because%20the%20cell%20148%20contains%20the%20value%203.%20If%20I%20change%20it%20to%204%2C%20the%20formula%20must%20be%3C%2FP%3E%3CP%3E%3D'%5BAttendance%20Y4.xlsx%5DAttendance%20register'!%24A%249%3A%24A%2429%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2710955%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2711162%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20one%20character%20within%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2711162%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1140785%22%20target%3D%22_blank%22%3E%40chamseddine_hamdeni%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20use%20INDIRECT%20for%20this%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DINDIRECT(%22'%5BAttendance%20Y%22%20%26amp%3B%20C152%20%26amp%3B%20%22.xlsx%5DAttendance%20register'!%24A%249%3A%24A%2429%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWarning%3A%20this%20will%20only%20work%20if%20the%20other%20workbook%20is%20open.%20INDIRECT%20cannot%20work%20with%20references%20to%20a%20closed%20workbook.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional 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.