• 580K Members
• 4,860 Online
• 702K Conversations

Highlighted
New Contributor

# Dynamic Workbook reference

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

# Re: Dynamic Workbook reference

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
Cannot enter sources
AndreasDK1982 in Office 365 on
0 Replies
Reference Microsoft Flow triggers and actions
dyota257 in Office 365 on
0 Replies
Share cell data between workbooks
gcooke75 in Excel on
3 Replies
Creating A Sublist
zjohnson in Excel on
5 Replies