How to update a master sheet containing data of other sheets

danhendo888

Board Regular
Joined
Jul 15, 2019
Messages
142
Office Version
  1. 365
Platform
  1. Windows

Book1
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?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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:
Upvote 0
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:
Upvote 0


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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top