I have a master spreadsheet that collates data from other spreadsheets, and shows this via linked cells - for example, I would have ten identically set out spreadsheets, each showing a different total value. This total value is then shown on my master spreadsheet using the formula
='C:\Project folder\Folder1\SubFolderA[PaymentInfo SubFolderA.xlsx]Sheet 1'!$B$5
The value that I want shown will always be on Sheet 1, cell B:5 of the spreadsheet that I'm linking to, but the filename and location will change. However, the filename and location will always reference other information on the master spreadsheet - so Folder1 will always be in column A of the masterspreadsheet, and SubFolderA will always be in Column C of the master spreadsheet.
Is there any way that I can automate/autofill the formulas, as manually editing each one is going to take forever (there are over 400 individual linked spreadsheets). Ideally, it would be something like:
='C:\Project folder\ Value from column A \ Value from column C \ [PaymentInfo Value from column C.xlsx]Sheet 1'!$B$5
I did Google the issue, but as I'm not sure what the terms are for what I'm looking for, I didn't get very far. I did find something on array formulas that looked as if it was what I needed, but when I tried it, I couldn't get it to work.
I'm using Excel 2010,