How to update a master sheet containing data of other sheets

danhendo888

Board Regular
Joined
Jul 15, 2019
Messages
126
Office Version
2016
Platform
Windows
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Sheet1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="background-color: #FFC000;;">Customer Number</td><td style="text-align: right;background-color: #FFC000;;">Invoice</td><td style="background-color: #FFC000;;">Date</td><td style="background-color: #FFC000;;">Due</td><td style="background-color: #FFC000;;">Amount</td><td style="background-color: #FFC000;;">Status</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Nike</td><td style="text-align: right;;">101</td><td style="text-align: right;;">1/01/2019</td><td style="text-align: right;;">1/02/2019</td><td style="text-align: right;;">100</td><td style=";">Booked</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Nike</td><td style="text-align: right;;">102</td><td style="text-align: right;;">2/01/2019</td><td style="text-align: right;;">2/02/2019</td><td style="text-align: right;;">200</td><td style=";">Paid</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Nike</td><td style="text-align: right;;">103</td><td style="text-align: right;;">3/01/2019</td><td style="text-align: right;;">3/02/2019</td><td style="text-align: right;;">300</td><td style=";">Booked</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Nike</td><td style="text-align: right;;">103</td><td style="text-align: right;;">3/01/2019</td><td style="text-align: right;;">3/02/2019</td><td style="text-align: right;;">300</td><td style=";">Booked</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Sheet2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="background-color: #FFC000;;">Customer Number</td><td style="text-align: right;background-color: #FFC000;;">Invoice</td><td style="background-color: #FFC000;;">Date</td><td style="background-color: #FFC000;;">Due</td><td style="background-color: #FFC000;;">Amount</td><td style="background-color: #FFC000;;">Status</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Adidas</td><td style="text-align: right;;">AAA</td><td style="text-align: right;;">1/07/2019</td><td style="text-align: right;;">1/08/2019</td><td style="text-align: right;;">10</td><td style=";">Not Booked</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Adidas</td><td style="text-align: right;;">BBB</td><td style="text-align: right;;">2/07/2019</td><td style="text-align: right;;">2/08/2019</td><td style="text-align: right;;">20</td><td style=";">Not Booked</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Adidas</td><td style="text-align: right;;">CCC</td><td style="text-align: right;;">3/07/2019</td><td style="text-align: right;;">3/08/2019</td><td style="text-align: right;;">30</td><td style=";">Not Booked</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Sheet3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="background-color: #FFC000;;">Customer Number</td><td style="text-align: right;background-color: #FFC000;;">Invoice</td><td style="background-color: #FFC000;;">Date</td><td style="background-color: #FFC000;;">Due</td><td style="background-color: #FFC000;;">Amount</td><td style="background-color: #FFC000;;">Status</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">Nike</td><td style="text-align: right;;">101</td><td style="text-align: right;;">1/01/2019</td><td style="text-align: right;;">1/02/2019</td><td style="text-align: right;;">100</td><td style=";">Booked</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">Nike</td><td style="text-align: right;;">102</td><td style="text-align: right;;">2/01/2019</td><td style="text-align: right;;">2/02/2019</td><td style="text-align: right;;">200</td><td style=";">Paid</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">Nike</td><td style="text-align: right;;">103</td><td style="text-align: right;;">3/01/2019</td><td style="text-align: right;;">3/02/2019</td><td style="text-align: right;;">300</td><td style=";">Booked</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">Nike</td><td style="text-align: right;;">103</td><td style="text-align: right;;">3/01/2019</td><td style="text-align: right;;">3/02/2019</td><td style="text-align: right;;">300</td><td style=";">Booked</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">Adidas</td><td style="text-align: right;;">AAA</td><td style="text-align: right;;">1/07/2019</td><td style="text-align: right;;">1/08/2019</td><td style="text-align: right;;">10</td><td style=";">Not Booked</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style=";">Adidas</td><td style="text-align: right;;">BBB</td><td style="text-align: right;;">2/07/2019</td><td style="text-align: right;;">2/08/2019</td><td style="text-align: right;;">20</td><td style=";">Not Booked</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">Adidas</td><td style="text-align: right;;">CCC</td><td style="text-align: right;;">3/07/2019</td><td style="text-align: right;;">3/08/2019</td><td style="text-align: right;;">30</td><td style=";">Not Booked</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br />

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?
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

danhendo888

Board Regular
Joined
Jul 15, 2019
Messages
126
Office Version
2016
Platform
Windows
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:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,742
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:

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,912
Office Version
2019
Platform
Windows


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
 

danhendo888

Board Regular
Joined
Jul 15, 2019
Messages
126
Office Version
2016
Platform
Windows
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.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,742
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
 

Watch MrExcel Video

Forum statistics

Threads
1,101,914
Messages
5,483,689
Members
407,399
Latest member
Rakeforms

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top