Results 1 to 7 of 7

Thread: How to update a master sheet containing data of other sheets

  1. #1
    Board Regular
    Join Date
    Jul 2019
    Location
    Win 10, Office Professional Plus 2016
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to update a master sheet containing data of other sheets

    ABCDEF
    1Sheet1
    2Customer NumberInvoiceDateDueAmountStatus
    3Nike1011/01/20191/02/2019100Booked
    4Nike1022/01/20192/02/2019200Paid
    5Nike1033/01/20193/02/2019300Booked
    6Nike1033/01/20193/02/2019300Booked
    7
    8Sheet2
    9Customer NumberInvoiceDateDueAmountStatus
    10AdidasAAA1/07/20191/08/201910Not Booked
    11AdidasBBB2/07/20192/08/201920Not Booked
    12AdidasCCC3/07/20193/08/201930Not Booked
    13
    14Sheet3
    15Customer NumberInvoiceDateDueAmountStatus
    16Nike1011/01/20191/02/2019100Booked
    17Nike1022/01/20192/02/2019200Paid
    18Nike1033/01/20193/02/2019300Booked
    19Nike1033/01/20193/02/2019300Booked
    20AdidasAAA1/07/20191/08/201910Not Booked
    21AdidasBBB2/07/20192/08/201920Not Booked
    22AdidasCCC3/07/20193/08/201930Not Booked

    Sheet1





    Suppose I have (where some cells contain formulas and column headers are same):
    Sheet 1 = contains Nike invoices info
    Sheet 2 = contains Adidas invoices info
    Sheet 3 = contains the data in Sheet 1 and Sheet 2

    I would like to update Sheet 1 and Sheet 2 on a daily basis but have Sheet 3 to update whatever rows I add to Sheet 1 or Sheet 2.
    How could I go about doing this?

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

    Default Re: How to update a master sheet containing data of other sheets

    use PowerQuery aka Get&Transform with Append feature
    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
    Board Regular
    Join Date
    Jul 2019
    Location
    Win 10, Office Professional Plus 2016
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to update a master sheet containing data of other sheets

    Quote Originally Posted by sandy666 View Post
    use PowerQuery aka Get&Transform with Append feature
    Ok, so I have managed to create a query using a table (Table1) such that when I update my table (Table1), I can refresh my other table (Table2), which is connected to the query, and have it show whatever new rows I am adding to Table1.

    But if I change the Number Format of a column in my original table (Table1), the query reports an error and the values in the table (Table2) connected to the query are blank.

    How do I fix this?
    Last edited by danhendo888; Jul 21st, 2019 at 11:59 PM.

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

    Default Re: How to update a master sheet containing data of other sheets

    say you've three tables:
    source: Table1, Table2
    result: will be

    Table1 and Table2 are loaded to PowerQuery and Append then the result Query Table load into the sheet

    You cannot determine too much formats in source, you can do that in Query Editor for each column or in result Query Table in the sheet.

    edit:
    I have incorrectly said, of course, that you can specify the format in the source table, but that does not have too much effect on the output format
    Last edited by sandy666; Jul 22nd, 2019 at 02:42 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

  5. #5
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    5,033
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to update a master sheet containing data of other sheets

    Quote Originally Posted by danhendo888 View Post


    I would like to update Sheet 1 and Sheet 2 on a daily basis but have Sheet 3 to update whatever rows I add to Sheet 1 or Sheet 2.
    How could I go about doing this?
    Hi,
    As an alternative thought, rather than spending time updating individual sheets which then need to update Sheet 3 (summary) why not just update Sheet 3 & filter records to the appropriate sheet which can be done using Advanced Filter Copy?


    Dave

  6. #6
    Board Regular
    Join Date
    Jul 2019
    Location
    Win 10, Office Professional Plus 2016
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to update a master sheet containing data of other sheets

    Quote Originally Posted by dmt32 View Post
    Hi,
    As an alternative thought, rather than spending time updating individual sheets which then need to update Sheet 3 (summary) why not just update Sheet 3 & filter records to the appropriate sheet which can be done using Advanced Filter Copy?


    Dave
    I'm trying to come up with a good way to manage information relating to customer invoices.
    Currently, I have a workbook with 20 sheets.
    Each sheet (1 to 19) has a table of data dedicated to one customer and has the following columns: customer number, invoice number, invoice amounts, booked status, paid status, payment date.
    The columns underlined are "calculated" using formulas based on extracted data that I paste onto the last sheet (Sheet 20) in the workbook.

    As you mentioned, I could just combine the tables in each sheet into one "master" table.
    I might try it but there are instances where I have to create new columns in order to drill down certain invoices that are problematic. If I have all the data in one table, it could get messy.
    But yeah, just not sure how best to go about it.
    Happy to take some thoughts/opinions from you guys.

    Thank you

    P.S I had to watch Excelisfun to understand what you meant by Advanced Filter Copy. Now I know! Thanks for mentioning it.

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

    Cool Re: How to update a master sheet containing data of other sheets

    could you tell / show what are you changing and what you want to achieve as the end result?

    I'd like to know whole procedure what are you doing there not only a small part

    here is a file based on your example from the post#1: download
    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
  •