I have created a folder with various sub-folders which contain a variety of workbooks on a thumb drive. I have a master workbook in the root folder and would like to create dynamic links to the the workbooks in those sub-folders.
The reason for the dynamic links is that when the thumb drive is moved to a different PC the associated drive letter changes. (additionally if the root folder and all sub-folders is copied from the thumb drive to a hard drive the associated path is changed.
In the master workbook my vision is to create a dynamic path to the respective workbooks in the various sub-folders and that concatenated path would be used along with the sheet name which I would want to retrieve the data from.
I am having issue writing a formula that would allow me to insert a dynamic link instead of a fixed path.
This fixed path works to retrieve data from one of the files in the sub-folder of "proposed". =IF(+'[_Contract Details.xlsx]Current'!$C5="","",+'[_Contract Details.xlsx]Current'!$C5) (Full path see attached image below)
My Concatenated formula in cell G4, for the file path is =(CONCATENATE('READ ME FIRST (INDEX)'!$A$7,"\",'Employee Contracts'!$F$4,"[",'Employee Contracts'!$F$6,"]","current!"))
This concatenation gives me the following path C:\Users\gerryp\Documents\proposed\Employee Contracts\[_Contract Details.xlsx]current!
I have the _Contract Details.xlsx file open yet I get a #REF! in the field where I have this formula. =INDIRECT(G4 &ADDRESS(ROW(A2),COLUMN(A3)))
Is there a formula string that I can utilize that would give me the ability to embed the dynamic path reference and the source file worksheet cell? I would also like to drag the formula down and over a few rows and columns without the need to manually change the cell addresses.
The reason for the dynamic links is that when the thumb drive is moved to a different PC the associated drive letter changes. (additionally if the root folder and all sub-folders is copied from the thumb drive to a hard drive the associated path is changed.
In the master workbook my vision is to create a dynamic path to the respective workbooks in the various sub-folders and that concatenated path would be used along with the sheet name which I would want to retrieve the data from.
I am having issue writing a formula that would allow me to insert a dynamic link instead of a fixed path.
This fixed path works to retrieve data from one of the files in the sub-folder of "proposed". =IF(+'[_Contract Details.xlsx]Current'!$C5="","",+'[_Contract Details.xlsx]Current'!$C5) (Full path see attached image below)
My Concatenated formula in cell G4, for the file path is =(CONCATENATE('READ ME FIRST (INDEX)'!$A$7,"\",'Employee Contracts'!$F$4,"[",'Employee Contracts'!$F$6,"]","current!"))
This concatenation gives me the following path C:\Users\gerryp\Documents\proposed\Employee Contracts\[_Contract Details.xlsx]current!
I have the _Contract Details.xlsx file open yet I get a #REF! in the field where I have this formula. =INDIRECT(G4 &ADDRESS(ROW(A2),COLUMN(A3)))
Is there a formula string that I can utilize that would give me the ability to embed the dynamic path reference and the source file worksheet cell? I would also like to drag the formula down and over a few rows and columns without the need to manually change the cell addresses.
