Assistance with Importing Data From Other Excel Files
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Assistance with Importing Data From Other Excel Files
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2016
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Assistance with Importing Data From Other Excel Files

    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!

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,574
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Assistance with Importing Data From Other Excel Files

    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.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #3
    New Member
    Join Date
    May 2016
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Assistance with Importing Data From Other Excel Files

    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.

  4. #4
    New Member
    Join Date
    May 2016
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Assistance with Importing Data From Other Excel Files

    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.

  5. #5
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,574
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Assistance with Importing Data From Other Excel Files

    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.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  6. #6
    New Member
    Join Date
    May 2016
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Assistance with Importing Data From Other Excel Files

    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.

  7. #7
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,574
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Assistance with Importing Data From Other Excel Files

    Quote Originally Posted by RudeClown View Post
    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 by sandy666; Jul 23rd, 2019 at 01:13 AM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  8. #8
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,133
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Assistance with Importing Data From Other Excel Files

    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.
    If you've posted a clearly explained question & sample data - both input & corresponding output - that can be copied to Excel, THANK YOU.
    Google can find answers to nearly every question.


  9. #9
    New Member
    Join Date
    May 2016
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Assistance with Importing Data From Other Excel Files

    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!

  10. #10
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,574
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Assistance with Importing Data From Other Excel Files

    Quote Originally Posted by RudeClown View Post
    I am interested in learning about how those options work and what I can do with them moving forward.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •