+ Reply to Thread
Results 1 to 14 of 14

Making an automated table in new sheet out of 10 sheets

  1. #1
    Registered User
    Join Date
    10-30-2018
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    30

    Making an automated table in new sheet out of 10 sheets

    Dear All,

    Can someone help me? I have 10 different Excel sheets in 1 workbook. This is some kind of stocklist.
    They are all built up the same way.

    Now I have an 11th sheet where I would like to combine all data in 1 sheet to have a global overview.
    So basically all data from All diferent sheets should be linked in Sheet "Globale Stocklijst".
    If I add/Update data in 1 of the other sheets, the data in Globale stocklijst should be updated automatically.

    Please find the file attached. And thanks for your help.
    Attached Files Attached Files

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Making an automated table in new sheet out of 10 sheets

    I'd recommend updating your profile. I don't think XP is an Office version.
    Depending on your version, recommended approach would differ.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    10-30-2018
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: Making an automated table in new sheet out of 10 sheets

    Done, I think office 365 - Excel 2010.

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Making an automated table in new sheet out of 10 sheets

    Office 365 then you are using Excel 2016/19.

    Using Office 365 you have access to Get&Transform/PowerQuery. This is your best tool for ETL (Extract, Transform, Load) operation.

    Set up:
    1. Add another sheet named "SetUp". Use CELL("filename") to get file name and folder path dynamically.
    2. Name the cell that has the full file path using Name manager. I named it vFile.
    3. Save file.
    4. Using Data -> Get & Transform Data -> Get Data -> From Other Sources. Create blank query.
    5. Go into advanced editor (found under view). Paste in following.
    Please Login or Register  to view this content.
    This will create table with all Sheets/Tables listed in workbook.

    6. Using [Name], filter out "Globale Stocklijst" and "SetUp". Select all but [Name] and [Data] columns and remove.
    7. Click on expand column icon on [Data], Uncheck "Use original column name as prefix".
    8. Select [Column2]. Filter out (null). Then select both [Column2] & [Column3] remove duplicates.
    9. Go to Transform tab and "Use First Row as Headers". Then remove the first column.
    10. Use File->Close & Load to... to load the data to sheet.

    When new data is added to any sheet. Save the file first. Then refresh table. It will update with new data.

    See attached.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-30-2018
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: Making an automated table in new sheet out of 10 sheets

    Hi

    Wow This looks nice indeed!
    But I guess i'm indeed not using Office 365 yet. I get this error when I'm trying to update the file.
    Is this error indeed because i'm using a different Excel version?

    If this is the case it will be solved soon as i'm going to switch my PC to a newer version.

    Thanks for your help
    Attached Images Attached Images

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Making an automated table in new sheet out of 10 sheets

    No, that message is not related to version of Excel.

    Did you first open the file, enable External Query, then save the file. Before you refreshed?
    You'll need to do so, if you haven't.

    If the issue persist...
    Hit "OK". Then go to Data -> Queries & Connections.

    Right click on the query and Edit. In the editor, go to File->Data source settings.
    In there find "Current Workbook" and click on Edit permissions. Set it to public.

  7. #7
    Registered User
    Join Date
    10-30-2018
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: Making an automated table in new sheet out of 10 sheets

    Hi,

    I will try this this evening. Thanks for your hlep.
    Just one more question. If I add extra columns in the sheets. Will the table be updated automatically too?

    Thanks.

  8. #8
    Registered User
    Join Date
    10-30-2018
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: Making an automated table in new sheet out of 10 sheets

    Or is the problem with updating that I don't have power query on my laptop?

  9. #9
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Making an automated table in new sheet out of 10 sheets

    Ah, I think I know the issue. You have PQ, but it's version isn't up to date.

    You should be able to follow instruction in post #4 and produce result. If you have specific issues. I can help.

  10. #10
    Registered User
    Join Date
    10-30-2018
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: Making an automated table in new sheet out of 10 sheets

    Hi CK,

    I'm trying.
    But don't seem to have this option:
    4. Using Data -> Get & Transform Data -> Get Data -> From Other Sources. Create blank query.

  11. #11
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Making an automated table in new sheet out of 10 sheets

    Hmm, can you check following?

    Go to File -> Options -> Add-ins

    Check Active ones. If PowerQuery isn't there. Then check COM Add-ins and see if you find it there and add it.

    If it isn't there, you can go to link below and download (assuming you have either Excel 2010/2013).
    https://www.microsoft.com/en-ca/down....aspx?id=39379

  12. #12
    Registered User
    Join Date
    10-30-2018
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: Making an automated table in new sheet out of 10 sheets

    Hey,

    I installed the power Query tool and now it seems to be updating. But when I change data in 1 of the fields, the global list don't get updated... So I guess something is still going wrong..

  13. #13
    Registered User
    Join Date
    10-30-2018
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: Making an automated table in new sheet out of 10 sheets

    Hi,

    Can anyone help me? I get the attached error message.. i'm using Excel 2010 with power query.
    Attached Images Attached Images
    Last edited by simpauw; 03-26-2019 at 10:48 AM.

  14. #14
    Registered User
    Join Date
    10-30-2018
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: Making an automated table in new sheet out of 10 sheets

    All,

    Fixed it myself by making a whole new powerquery file.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help making a process more automated!
    By hrabbani in forum Excel General
    Replies: 3
    Last Post: 08-16-2017, 11:36 AM
  2. Making a formula automated
    By JT_2111 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-17-2014, 12:54 PM
  3. Making fully automated spreadsheet
    By chestersneakers7 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-14-2013, 02:38 PM
  4. Making automated reports
    By fbplaya02 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-14-2013, 11:32 PM
  5. Replies: 5
    Last Post: 01-25-2013, 08:04 AM
  6. Making 2 sheets on 1 sheet
    By racecannon in forum Excel General
    Replies: 2
    Last Post: 11-04-2011, 04:32 PM
  7. help! making a worksheet more automated?
    By redb in forum Excel General
    Replies: 2
    Last Post: 04-15-2005, 09:06 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1