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
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
36 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies