I am currently trying to set up a spreadsheet which will draw in data from several files (in several folders) into one ‘master’ sheet.
I have drawn the filenames for the data spreadsheets into the master sheet, as below (Fig.1), using the INDEX function.
Fig.1
1.jpg
I want the end product to look like this (Fig.2):
Fig.2
2.jpg
In each file, the data for each individual column is in a consistent cell number, and in a consistent format. I have been able to draw in the data by manually typing the filename (e.g. ='[AB001-001.xlsx]Sheet1!$A$1), however, I haven’t been able to link the formula to the filename in the left-hand column.
Is there a way to make this work, or possibly an alternative route which could work better?
I looked into setting up a Query which drew in the data into the ‘master’ spreadsheet well, however returned the data in multiple rows for a single filename as below (Fig.3).
Fig.3
3.jpg
I then couldn’t work out how to draw the data from Fig.3 into the format shown in Fig.2.
If anyone can help or if more details are needed, please let me know!
Bookmarks