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!

 

[RudeClown]

Actually, I was able to find a simple solution to my original issue. I figured out how to import my data to the Log tab, but that led to another problem. Then I figured out that using "Replace All" works for formulas on a sheet also. I swear I had tried that before and it didn't work, but regardless, it does. So I can import to a new tab and easily fix the formulas on my Summary page. I do however have a new issue I've run into, which I will post separately as it is a different issue. BTW, I was using the PowerQuery the whole time to import the data, I just didn't realize that was what it is called. I'm not all that familiar with the capabilities though, so I will still be digging into it. Thank you for your help!.

 

[sandy666]

I was using the PowerQuery the whole time to import the data, I just didn't realize that was what it is called. I'm not all that familiar with the capabilities though, so I will still be digging into it.

This is a good news

I wonder why you "duplicate" data (LOG tab) if you can transform and calculate inside PowerQuery/PowerPivot and load the result(s) into the Summary tab without formula(s) but this is your choice of course