Dynamic linking with multiple spreadsheets

  • I've been trying to find an easier way to link thousands of spreadsheets filled with data using a single workbook that pulls specific information into one source. I have column A listing thousands of store numbers statically with one store per row. Columns B-BZ then use hard links to thousands of individual files for each store number that's saved on my flash drive to pull the data needed. This works perfectly ok but if I change the store number in column A, the other columns won't update because of the link.


    Example:

    A1 = 1 B1='J:\Data\2021\Dec\[1.xlsx]Prelim'!$D$7 C1='J:\Data\2021\Dec\[1.xlsx]Audit Entry'!$M$10

    A2 = 2 B2='J:\Data\2021\Dec\[2.xlsx]Prelim'!$D$7 C2='J:\Data\2021\Dec\[2.xlsx]Audit Entry'!$M$10

    A3 = 3 B3='J:\Data\2021\Dec\[3.xlsx]Prelim'!$D$7 C3='J:\Data\2021\Dec\[3.xlsx]Audit Entry'!$M$10


    Is it possible to dynamically link the external worksheet such as [1.xlsx] somehow? I tried entering as [A1.xlsx] but wouldn't work and haven't been able to find a solution. Any assistance would be greatly appreciated.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!