How to update a master sheet containing data of other sheets

[danhendo888]

Book1
	A	B	C	D	E	F
1	Sheet1
2	Customer Number	Invoice	Date	Due	Amount	Status
3	Nike	101	1/01/2019	1/02/2019	100	Booked
4	Nike	102	2/01/2019	2/02/2019	200	Paid
5	Nike	103	3/01/2019	3/02/2019	300	Booked
6	Nike	103	3/01/2019	3/02/2019	300	Booked
7
8	Sheet2
9	Customer Number	Invoice	Date	Due	Amount	Status
10	Adidas	AAA	1/07/2019	1/08/2019	10	Not Booked
11	Adidas	BBB	2/07/2019	2/08/2019	20	Not Booked
12	Adidas	CCC	3/07/2019	3/08/2019	30	Not Booked
13
14	Sheet3
15	Customer Number	Invoice	Date	Due	Amount	Status
16	Nike	101	1/01/2019	1/02/2019	100	Booked
17	Nike	102	2/01/2019	2/02/2019	200	Paid
18	Nike	103	3/01/2019	3/02/2019	300	Booked
19	Nike	103	3/01/2019	3/02/2019	300	Booked
20	Adidas	AAA	1/07/2019	1/08/2019	10	Not Booked
21	Adidas	BBB	2/07/2019	2/08/2019	20	Not Booked
22	Adidas	CCC	3/07/2019	3/08/2019	30	Not 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?
​

 

[sandy666]

use PowerQuery aka Get&Transform with Append feature

 

[danhendo888]

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?

 

[sandy666]

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

 

[dmt32]

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]

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]

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