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
