Home

Dynamic Workbook reference

%3CLINGO-SUB%20id%3D%22lingo-sub-790328%22%20slang%3D%22en-US%22%3EDynamic%20Workbook%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-790328%22%20slang%3D%22en-US%22%3E%3CP%3Ehello%20everyone!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20to%20conduct%20a%20study%2C%20where%20I%20have%20to%20import%20data%20into%20an%20Excel%20Sheet%20(main%20file)%20from%20461%20other%20excel%20files.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDepending%20on%20conditions%2C%20different%20columns%20are%20imported%20into%20the%20main%20Excel%20sheet.%20I%20managed%20to%20do%20it%20with%20the%20following%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E%3DIF(IF(Salvation!%24AL%242%3DSalvation!%24AR%242%2C%E2%80%99C%3A%5CUsers%5Cbill%5CDesktop%5CDataBase%5C%5BNAME.xlsx%5DQ'!%24A2%2CIF(Salvation!%24AL%242%3DSalvation!%24AR%243%2C%E2%80%99C%3A%5CUsers%5Cbill%5CDesktop%5CDataBase%5C%5BNAME.xlsx%5DQ'!%24D2%2CIF(Salvation!%24AL%242%3DSalvation!%24AR%244%2C%E2%80%99C%3A%5CUsers%5Cbill%5CDesktop%5CDataBase%5C%5BNAME.xlsx%5DQ'!%24G2%2CIF(Salvation!%24AL%242%3DSalvation!%24AR%245%2C%E2%80%99C%3A%5CUsers%5Cbill%5CDesktop%5CDataBase%5C%5BNAME.xlsx%5DQ'!%24H2%2CIF(Salvation!%24AL%242%3DSalvation!%24AR%246%2C%E2%80%99C%3A%5CUsers%5Cbill%5CDesktop%5CDataBase%5C%5BNAME.xlsx%5DQ'!%24I2%2CIF(Salvation!%24AL%242%3DSalvation!%24AR%247%2C%E2%80%99C%3A%5CUsers%5Cbill%5CDesktop%5CDataBase%5C%5BNAME.xlsx%5DQ'!%24E2%2CIF(Salvation!%24AL%242%3DSalvation!%24AR%248%2C%E2%80%99C%3A%5CUsers%5Cbill%5CDesktop%5CDataBase%5C%5BNAME.xlsx%5DQ'!%24C2%2C%22ERROR%22)))))))%26gt%3B0%2CIF(Salvation!%24AL%242%3DSalvation!%24AR%242%2C%E2%80%99C%3A%5CUsers%5Cbill%5CDesktop%5CDataBase%5C%5BNAME.xlsx%5DQ'!%24A2%2CIF(Salvation!%24AL%242%3DSalvation!%24AR%243%2C%E2%80%99C%3A%5CUsers%5Cbill%5CDesktop%5CDataBase%5C%5BNAME.xlsx%5DQ'!%24D2%2CIF(Salvation!%24AL%242%3DSalvation!%24AR%244%2C%E2%80%99C%3A%5CUsers%5Cbill%5CDesktop%5CDataBase%5C%5BNAME.xlsx%5DQ'!%24G2%2CIF(Salvation!%24AL%242%3DSalvation!%24AR%245%2C%E2%80%99C%3A%5CUsers%5Cbill%5CDesktop%5CDataBase%5C%5BNAME.xlsx%5DQ'!%24H2%2CIF(Salvation!%24AL%242%3DSalvation!%24AR%246%2C%E2%80%99C%3A%5CUsers%5Cbill%5CDesktop%5CDataBase%5C%5BNAME.xlsx%5DQ'!%24I2%2CIF(Salvation!%24AL%242%3DSalvation!%24AR%247%2C%E2%80%99C%3A%5CUsers%5Cbill%5CDesktop%5CDataBase%5C%5BNAME.xlsx%5DQ'!%24E2%2CIF(Salvation!%24AL%242%3DSalvation!%24AR%248%2C%E2%80%99C%3A%5CUsers%5Cbill%5CDesktop%5CDataBase%5C%5BNAME.xlsx%5DQ'!%24C2%2C%22ERROR%22)))))))%2C%22%22)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3EInside%20the%20main%20Excel%2C%20I%20have%20in%20a%20cell%20the%20NAME%20of%20the%20file%20I%20want%20to%20import%20the%20data%20from.%20I%20tried%20the%20INDIRECT%20but%20it%20wont%20work%20with%20the%20file%20closed.%20%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3EIs%20there%20any%20way%2C%20that%20the%20NAME%20can%20be%20added%20in%20the%20formula%20dynamically%3F%26nbsp%3B%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3EThank%20you%20in%20advance%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3EBill%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-790328%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%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-790848%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20Workbook%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-790848%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F124862%22%20target%3D%22_blank%22%3E%40Bill%20Zarvalias%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20concatenate%20the%20cell%20reference%20with%20the%20file%20path.%20For%20example%2C%20let's%20say%20your%20file%20name%20is%20in%20cell%20A1%2C%20then%20you%20could%20write%20the%20first%20portion%20of%20your%20formula%20as%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DIF(IF(%22Salvation!%24AL%242%3DSalvation!%24AR%242%2C%E2%80%99C%3A%5CUsers%5Cbill%5CDesktop%5CDataBase%5C%5B%3CSTRONG%3E%22%26amp%3BA1%26amp%3B%22%3C%2FSTRONG%3E.xlsx%5DQ'!%24A2...%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EHope%20this%20helps!%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EPReagan%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Bill Zarvalias
New Contributor

hello everyone!

 

I have to conduct a study, where I have to import data into an Excel Sheet (main file) from 461 other excel files.

 

Depending on conditions, different columns are imported into the main Excel sheet. I managed to do it with the following formula:

 

=IF(IF(Salvation!$AL$2=Salvation!$AR$2,’C:\Users\bill\Desktop\DataBase\[NAME.xlsx]Q'!$A2,IF(Salvation!$AL$2=Salvation!$AR$3,’C:\Users\bill\Desktop\DataBase\[NAME.xlsx]Q'!$D2,IF(Salvation!$AL$2=Salvation!$AR$4,’C:\Users\bill\Desktop\DataBase\[NAME.xlsx]Q'!$G2,IF(Salvation!$AL$2=Salvation!$AR$5,’C:\Users\bill\Desktop\DataBase\[NAME.xlsx]Q'!$H2,IF(Salvation!$AL$2=Salvation!$AR$6,’C:\Users\bill\Desktop\DataBase\[NAME.xlsx]Q'!$I2,IF(Salvation!$AL$2=Salvation!$AR$7,’C:\Users\bill\Desktop\DataBase\[NAME.xlsx]Q'!$E2,IF(Salvation!$AL$2=Salvation!$AR$8,’C:\Users\bill\Desktop\DataBase\[NAME.xlsx]Q'!$C2,"ERROR")))))))>0,IF(Salvation!$AL$2=Salvation!$AR$2,’C:\Users\bill\Desktop\DataBase\[NAME.xlsx]Q'!$A2,IF(Salvation!$AL$2=Salvation!$AR$3,’C:\Users\bill\Desktop\DataBase\[NAME.xlsx]Q'!$D2,IF(Salvation!$AL$2=Salvation!$AR$4,’C:\Users\bill\Desktop\DataBase\[NAME.xlsx]Q'!$G2,IF(Salvation!$AL$2=Salvation!$AR$5,’C:\Users\bill\Desktop\DataBase\[NAME.xlsx]Q'!$H2,IF(Salvation!$AL$2=Salvation!$AR$6,’C:\Users\bill\Desktop\DataBase\[NAME.xlsx]Q'!$I2,IF(Salvation!$AL$2=Salvation!$AR$7,’C:\Users\bill\Desktop\DataBase\[NAME.xlsx]Q'!$E2,IF(Salvation!$AL$2=Salvation!$AR$8,’C:\Users\bill\Desktop\DataBase\[NAME.xlsx]Q'!$C2,"ERROR"))))))),"")

 

Inside the main Excel, I have in a cell the NAME of the file I want to import the data from. I tried the INDIRECT but it wont work with the file closed.

Is there any way, that the NAME can be added in the formula dynamically? 

Thank you in advance

Bill

 

 

1 Reply

Hello @Bill Zarvalias,

 

You could concatenate the cell reference with the file path. For example, let's say your file name is in cell A1, then you could write the first portion of your formula as:

 

=IF(IF("Salvation!$AL$2=Salvation!$AR$2,’C:\Users\bill\Desktop\DataBase\["&A1&".xlsx]Q'!$A2...

 

Hope this helps!

PReagan

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies