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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

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,858
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,925
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,858
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,102,872
Messages
5,489,428
Members
407,687
Latest member
NeoSez

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top