1

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,

TIA

  • 1
  • Thanks - how would that work within my already existing formula though? It looks like a good way of referencing specific information, but I'm not sure how it would help find that information in the first place. Also, it states that if INDIRECT is used to reference another workbook, that workbook must be open - that won't be the case (otherwise I'd have to have over 400 workbooks open at a time!), so is there a way around that? – themightyshrub Apr 10 '18 at 11:08
  • An example formula: =Indirect("'C:\Project folder\ "&A1&" \ "&A2&" \ [PaymentInfo "&C2&".xlsx]Sheet 1'!$B$5. Unfortunately I'm not aware of any workaround for opening workbooks. You may use a macro which opens the workbook, gets the data, then closes it. – Máté Juhász Apr 10 '18 at 11:11
  • Brilliant, thank you, I'll try that out :) – themightyshrub Apr 10 '18 at 11:15

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.