Home

Inserting a filename into a cell and using that cell in a formula

%3CLINGO-SUB%20id%3D%22lingo-sub-763635%22%20slang%3D%22en-US%22%3EInserting%20a%20filename%20into%20a%20cell%20and%20using%20that%20cell%20in%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-763635%22%20slang%3D%22en-US%22%3E%3CDIV%3EI%20have%20the%20following%20formula%20in%20a%20cell%20that%20returns%20the%20correct%20value.%3C%2FDIV%3E%3CDIV%3E%3DIF(%24D10%3D%22%22%2C%22%22%2CIFERROR(INDEX(%5BFilename.xls%5DSHEET1!%24L%3A%24O%2C%24Z10%2C2)%2C%22%22))%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CP%3EThis%20formula%20is%20used%20many%20times%20in%20the%20worksheet%20so%20I%20want%20to%20replace%20the%20%5BFilename.xls%5D%20with%20a%20cell%20containing%20the%20filename.%26nbsp%3B%20That%20way%20when%20I%20reference%20a%20different%20workbook%20I%20only%20have%20to%20change%20the%20filename%20once%20instead%20of%20many%20times.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20%5BFilename.XLS%5D%20in%20cell%20K1%20and%20I%20have%20%5BFilename.XLS%5DSHEET1%20in%20cell%20K2.%3C%2FP%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CP%3EI%20need%20the%20%3DIF%20formula%20to%20get%20the%20filename%20from%20cell%20K1%20or%20the%20filename%20and%20sheet%20name%20from%20cell%20K2%20(either%20way%20works%20fine%20for%20me)%20and%20index%20that.%26nbsp%3B%20I've%20been%20trying%20to%20use%20variations%20of%20%3DINDIRECT(%22%22%26amp%3B%24K%242%26amp%3B%22)%2C%20but%20can't%20make%20it%20work%20either%20way.%26nbsp%3B%20Can%20someone%20please%20tell%20me%20how%20to%20do%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help.%3C%2FP%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-763635%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-763675%22%20slang%3D%22en-US%22%3ERe%3A%20Inserting%20a%20filename%20into%20a%20cell%20and%20using%20that%20cell%20in%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-763675%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F378603%22%20target%3D%22_blank%22%3E%40KSanderson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3ETo%20extract%20the%20File%20name%20in%20a%20Cell%2C%20Say%20A1%2C%20type%3C%2FP%3E%3CP%3E%3DCell(%22filename%22)%3C%2FP%3E%3CP%3ETo%20use%20it%20in%20a%20formula%20say%20in%20A2%20I%20just%20want%20to%20keep%20the%20Sheet%20name%2C%20Type%20in%20A2%3C%2FP%3E%3CP%3E%3DREPLACE(A1%2C1%2CFIND(%22%5D%22%2CA1)%2C%22%22)%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E
KSanderson
Occasional Visitor
I have the following formula in a cell that returns the correct value.
=IF($D10="","",IFERROR(INDEX([Filename.xls]SHEET1!$L:$O,$Z10,2),""))
 

This formula is used many times in the worksheet so I want to replace the [Filename.xls] with a cell containing the filename.  That way when I reference a different workbook I only have to change the filename once instead of many times.

 

I have [Filename.XLS] in cell K1 and I have [Filename.XLS]SHEET1 in cell K2.

 

I need the =IF formula to get the filename from cell K1 or the filename and sheet name from cell K2 (either way works fine for me) and index that.  I've been trying to use variations of =INDIRECT(""&$K$2&"), but can't make it work either way.  Can someone please tell me how to do this?

 

Thanks for your help.

1 Reply

@KSanderson 

Hi

To extract the File name in a Cell, Say A1, type

=Cell("filename")

To use it in a formula say in A2 I just want to keep the Sheet name, Type in A2

=REPLACE(A1,1,FIND("]",A1),"")

Hope that helps

Nabil Mourad