Hi I am new to this forum and haven't done Excel seriously for a number of years. So need some help and advice please for the best way of doing this.
I have several workbooks that have data in which I want to copy to another workbook so it is all in one workbook. So lets say Workbooks A to J have the data and Workbook Data is where I want the data to go.
In Workbook Data I have four cellls with a SUMIF functions, per row, for the results to go in to. The problem is I don't want to open Workbooks A to J to get the data for Workbook Data. Can this be done. Then I need to do the same thing for workbooks B to J.
In my example spreadsheets it works perfectly if you open the Worksheets A to J and then open Workbook Data and click the Update option when you open it. But if the other workbooks are not open the the cells all have #VALUE! in them.
All the files on my computer are at this location F:BackupBST ExcelRisk
Any help would be appreciated.
Dave
Hi,
My recommendation is to use Power Query, but below answer is how to reference using a formula.
To reference to a range in another workbook you write like below example.
So if you want to use SUMIF function you just wrap the reference inside the brackets.
/Anders
Thanks Anders
That is the formula I have and if the workbook is open that works but can I use a formula when the workbooks are closed so I don’t have to open all 12 of them.
Dave
Hello,
Please try with adding the full path to the file.
=SUMIF('F:BackupBST ExcelRisk[Workbook-A.xlsx]WBA'!D4:D11,5,'F:BackupBST ExcelRisk[Workbook-A.xlsx]WBA'!B4:B11)
You can find more info about external reference here.
/Anders
Dave,
If you want to go the Power Query route as Anders suggested - see the attached workbook.
I built the query pointing to a folder on my network and they changed it to your folder path, so I am no longer able to refresh it. However, you'll just need to refresh the query once you open the file and it should work as long as your underlying files are stored in the folder that you gave in your question.
With Power Query you don't need the underlying files to be opened.
Blanka
Thanks Blanka
But this didn't do anything I opened it in Excel and saw all the #Value! in all the cells and the formula in the cell were like this
=SUMIF('https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-file-uploads/58davew/2018/11/[Workbook A.xlsx]WBA'!$D$4:$D$11,D3,'https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-file-uploads/58davew/2018/11/[Workbook A.xlsx]WBA'!$C$4:$C$11)
I clicked on refresh all data and get errors.
How does Power Query work as I have never heard of it before?
Dave
Hi David,
In the blog section you can search for Power Query and you will find good information and examples on how to work with that tool. https://www.myonlinetraininghub.com/combine-excel-worksheets-with-power-query <-- is one of many.
/Anders
David,
The Power Query output is in columns K through O. I did not touch the formulas in your original file. All the cells had the #Value! error at my end, too.
If you don't like the layout that Power Query spits out, you can enter formulas in your original table pointing to Power Query table.
The beautiful thing about Power Query is that if you add more files to your folder or if you change values in your existing files and then you refresh Power Query, you will get updated values in your Workbook-Data worksheet.
Mynda has an excellent Power Query course. I took a while ago and it changed my professional life.
Good luck!
Blanka
