Assistance with Importing Data From Other Excel Files

[RudeClown]

I am working on a project where I am creating 6 Excel files. Five will be used by individuals to input data, and the 6th will be a “Master,” where I will import data from the 5 files used by the individuals to the Master to aggregate and summarize the data. On the Master, I have a “Log” tab, and a “Summary” tab. My plan is to put the 5 files into one folder (on a shared drive) and use the Data>Get Data>From File>From Folder function to import the data from the 5 files to the “Log” tab on my master, then my “Summary” tab will summarize the data as I want to see it. Then I can hit “Refresh Data” to update the “Log” information to the master at any time and thereby updating the Summary. The issue I am having, is that since Excel creates a new tab within my master document to display the data it pulls from the other files, my Summary tab isn’t reading any of the data obviously because the formulas reference the data being on my “Log” tab. I’ve tried deleting and renaming the tabs,but that just jacks up the cell references in all my formulas. Is there a way I can get Excel to import the data from the other files onto my existing “Log” tab so the formulas on the “Summary”page can read it from there? Or its here another ay I can accomplish this? I know I can do the import, name the new tab, then change all the formulas on my “Summary”page, but that will be a great deal of work and it seems like there should be an easier way to accomplish his. Any advice would be truly appreciated!

 

[sandy666]

Your five users updating tables on their files or creating new tables every time?
if updating only so just Refresh your data on your main file.
I don't understand why you creating Log if you can create Summary once and Refresh only, without any formulas.

All depends how your whole files/data is organised.

 

[RudeClown]

The five files will be added to daily, as they are a runninglog of incoming phone calls. I amattempting to set it up where I can simply refresh the “Log” on my Master file,which I can do, the issue is that my “Summary” reads from the “Log” tab in mymaster file. That tab has not yet hadany data imported to it. I can importthe data easy enough, but it puts it on a separate tab where the formulas on my“Summary tab” won’t read it, because I wrote them to read off the “Log”tab.

 

[RudeClown]

Additionally, I don't want the formulas reading directly from the 5 files because someday there may be 6 files, or they may have different names as coworkers may come and go. The 5 are dedicated to one person each, so setting it up to import a folder's worth of files resolves that rather than pulling from specific files.

 

[sandy666]

so after 30 days you have 150 files?

anyway, I assume all files have the same format and data type so you can use From Folder option then merge/append tables, transform as you wish for Summary table(s) and load into the sheet without any formulas.

in theory it should work if you add new file or remove any old file. I am guessing only because I don't know structure of your files and expected result.

 

[RudeClown]

No, there won't ever be 150 files. There's one file per person, and there will likely never be more than 6-7 files that I would need to read from. My intention is to do exactly what you suggest, and update the "Log" tab periodically to pull in the new data from the other files. That's not my issue. On my "Master" file, I have a "Log" tab and a "Summary" tab. The "Summary" tab uses the data from the "Log" tab, which is also in the Master file, to report back the numbers I want, consolidating the data from the "Log" tab. The problem, is that I have written the formulas on the Summary tab prior to importing any data. When I import the data for the first time, it is imported to a third, new tab. My formulas on the "Summary" tab don't reference that tab, they reference the "Log" tab, so my Summary displays no data. One solution is to do the import, then modify all of the formulas to the new tab, however there are probably 300 formulas on the page, and I don't want to do that if I don't have to just because it's time consuming. I am hoping to find a way to import to my existing "Log" tab, instead of importing to a new third tab.

 

[sandy666]

The "Summary" tab uses the data from the "Log" tab, which is also in the Master file, to report back the numbers I want, consolidating the data from the "Log" tab.

I'll try again:

If you'll use PowerQuery (Get&Transform) and From Folder option, you'll get data from all external files into PowerQuery Editor, transform it as you wish then load into the Master sheet directly without any Log tab and many formulas.

by using PowerQuery you need to change the way of thinking

 

[Fazza]

And if you're in an earlier version of Excel without PowerQuery (aka Get&Transform) just use a query. Or go straight to a pivot table. Again with the idea of no Log worksheet, and potentially no formulas.

When the data files change, just refresh the query. Or set it to refresh on file open - doesn't need code. Excel has had this functionality for 20+ years.

You may have trouble refreshing the query/pivot table if the source data files are open. There are workarounds.

 

[RudeClown]

I've never used PowerQuery before and have no familiarity with how to use it. I'm fledgling still with the way I was trying to do it. From a time perspective of when this project needs to be done by, I will probably have to do it the way I intended to start off with rather than starting from scratch with unfamiliar fuctionality, but I am interested in learning about how those options work and what I can do with them moving forward. I will do some searching on that to learn. Thank you!

 

[sandy666]

I am interested in learning about how those options work and what I can do with them moving forward.

• What is PowerQuery?
• Getting Started with Get & Transform in Excel
• Microsoft Power Query for Excel Help