Assistance with Importing Data From Other Excel Files

RudeClown

Board Regular
Joined
May 31, 2016
Messages
56
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!
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
so after 30 days you have 150 files? :devilish:

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.
 
Upvote 0
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.
 
Upvote 0
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 :)
 
Last edited:
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top