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

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies