Duplicate data to another sheet and copy to update when master is changed

[Ana_P]

Hi

I have been tasked with creating a duplicate of a pre-existing data table in Excel to another sheet in the workbook to sort, filter and display differently to the master sheet.

A few things to note:
- the copy needs to update when the master is changed
- blank cells in the master are to remain blank in the copy also (rather than pre-fill with 0)
- the data will be sorted into weeks and a new row will be needed below the data for each week to add subtotals to certain columns
- I would like formatting to copy through also
- new data will be added to the bottom of the master table and this will need to flow through to the copy also

I have tried paste link which I had to do in sections as the table was too big to do in one go - however this raised the issue with the blank cells showing values.
I then tried the formula IF(ISBLANK(A1),"",A1) however instead of A1 I clicked on the actual cell in the master table to use the structured reference with Excel tables, however when I tried to drag the formula to the rest of the cells the structured reference wouldn't change as necessary.

And then I got stuck and not sure where to go from here to achieve the result required - please help

Thanks

 

[OaklandJim]

Sorry, for me the objective is not totally clear. Is data copied just for one week or is any new data copied? So the copy sheet must have a summary row for each week?

So we do not have to try to create a workbook from scratch it would sure be nice if you could post a link to yours.

 

[Ana_P]

Hi,

The whole table needs to be copied and then the copy will be sorted and filtered as needed.

And yes, the copy sheet must have a summary row for each week.

If any changes, including new entries, are made on the master, then the copy will need to reflect those changes.

Will provide a link to my workbook shortly.

Thanks

 

[OaklandJim]

I do see one potential challenge. You say that the "copy" will include rows with sums for each week. If you sort or filter you may not get the expected results, or am I missing something?

This formula -- IF(ISBLANK(A1),"",A1) -- would seem to be unhelpful as it says if the cell is empty then put empty into the cell, otherwise show A1.

Maybe this would help: If(AND(A1<>"", A1<>0), A1, ""). When you copy it down or across the reference to A1 should adjust.

 

[OaklandJim]

I apologize, I guess your formula is ok. My response was too hasty.

 

[Ana_P]

I do see one potential challenge. You say that the "copy" will include rows with sums for each week. If you sort or filter you may not get the expected results, or am I missing something?

This formula -- IF(ISBLANK(A1),"",A1) -- would seem to be unhelpful as it says if the cell is empty then put empty into the cell, otherwise show A1.

Maybe this would help: If(AND(A1<>"", A1<>0), A1, ""). When you copy it down or across the reference to A1 should adjust.

So I used the formula =IF(ISBLANK(INDEX('July-Dec 2022'!$A:$AB,ROW(),COLUMN())),"",INDEX('July-Dec 2022'!$A:$AB,ROW(),COLUMN())) to copy the info across from one sheet to the other. However, once I got to the sorting/filtering realised that this method is not the way I need to go about it as the formula basically keeps the data in the same format as the master copy.

In the copy I need to sort by Year, Schedule Week, Schedule Date, then F/Y column (in that order and from smallest to largest, or oldest to newest). Then I require a subtotal based on Schedule Week to add totals to Metres In, Metres Out, Kgs In, Kgs Out.

Here is some of the data table:

Sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Date Raised	Date Delivered	F/Y	CWO	Finisher	Add LT	Customer	Customer PO	Comments	Due Date	Revised ETA	Schedule Date	Schedule Week	Date Received
93	18/01/22	18/01/22	F	22000	AB		GHI			1/02/22		1/02/22	5	25/01/22
94	18/01/22	18/01/22	F	22001	AB		PQR			1/02/22		1/02/22	5	3/02/22
95	18/01/22	18/01/22	F	22001	AB		PQR			1/02/22		1/02/22	5	3/02/22
96	18/01/22	18/01/22	F	22001	AB		PQR			1/02/22		1/02/22	5	3/02/22
97	18/01/22	18/01/22	F	22004	AB		PQR			1/02/22		1/02/22	5	3/02/22
98	18/01/22	18/01/22	F	22005	AB		AZ			1/02/22		1/02/22	5	16/02/22
99	18/01/22	18/01/22	F	22008	AB		GHI			1/02/22		1/02/22	5	31/01/22
100	18/01/22	18/01/22	F	22008	AB		GHI			1/02/22		1/02/22	5	31/01/22
101	18/01/22	18/01/22	F	22008	AB		GHI			1/02/22		1/02/22	5	31/01/22
102	18/01/22	18/01/22	F	22008	AB		GHI			1/02/22		1/02/22	5	31/01/22
103	18/01/22	18/01/22	F	22009	AB		ABC			1/02/22		1/02/22	5	1/02/22
104	18/01/22	18/01/22	F	22009	AB		ABC			1/02/22		1/02/22	5	1/02/22
105	19/01/22	19/01/22	F	22011	AB		OP8			2/02/22		2/02/22	5	11/02/22
106	20/01/22	24/01/22	F	22012	AB		JKL			7/02/22		7/02/22	6	17/02/22
107	20/01/22	24/01/22	F	22012	AB		JKL			7/02/22		7/02/22	6	17/02/22
108	20/01/22	31/01/22	F	22012	AB		JKL			14/02/22		14/02/22	7	25/02/22
109	29/03/22	29/03/22	F	22012	AB		JKL			12/04/22		12/04/22	15	14/04/22
110	20/01/22	2/02/22	F	22013	AB		JKL			16/02/22		16/02/22	7	25/02/22
111	20/01/22	21/01/22	F	22014	AB		JKL			4/02/22		4/02/22	5	30/05/22
112	20/01/22	29/03/22	F	22014	AB		JKL			12/04/22		12/04/22	15	12/04/22
113	20/01/22	19/01/22	F	22015	AB		JKL			2/02/22		2/02/22	5	10/02/22
114	20/01/22	31/01/22	F	22016	AB		YZ			14/02/22		14/02/22	7	4/02/22
115	20/01/22	31/01/22	F	22016	AB		YZ			14/02/22		14/02/22	7	17/02/22
116	25/01/22	25/01/22	F	22018	AB		GHI			8/02/22		8/02/22	6	28/01/22
117	25/01/22	25/01/22	F	22018	AB		GHI			8/02/22		8/02/22	6	28/01/22
118	25/01/22	25/01/22	F	22018	AB		GHI			8/02/22		8/02/22	6	1/02/22
119	25/01/22	25/01/22	F	22018	AB		GHI			8/02/22		8/02/22	6	14/02/22
120	25/01/22	25/01/22	F	22018	AB		GHI			8/02/22		8/02/22	6	14/02/22
121	25/01/22	25/01/22	F	22019	AB		AZ			8/02/22		8/02/22	6	16/02/22
122	25/01/22	25/01/22	F	22020	AB		PQR			8/02/22		8/02/22	6	3/02/22
123	25/01/22	25/01/22	F	22021	CD		GHI			8/02/22		8/02/22	6	4/02/22
124	25/01/22	25/01/22	F	22021	CD		GHI			8/02/22		8/02/22	6	4/02/22
125	25/01/22	25/01/22	F	22021	CD		GHI			8/02/22		8/02/22	6	4/02/22
July-Dec 2022

Cell Formulas
Range	Formula
L93:L125	L93	=IF(ISBLANK([@[Revised ETA]]),[@[Due Date]],[@[Revised ETA]])
M93:M125	M93	=IF(OR([@[Schedule Date]]="",[@[Schedule Date]]="Missing Info"),"",ISOWEEKNUM([@[Schedule Date]]))
J93:J125	J93	=IF(ISBLANK([@[Date Raised]]),"",IF(OR(ISBLANK([@[Date Delivered]]),ISBLANK([@[F/Y]])),"Missing Info",[@[Date Delivered]]+VLOOKUP([@[F/Y]],Lead_Time,3,FALSE)+[@[Add LT]]))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2:I601	Expression	=AND($Q2="Open",ISNUMBER(SEARCH("ASAP",$I2)))	text	NO
I2:I601	Expression	=AND($Q2="Open",ISNUMBER(SEARCH("Urgent",$I2)))	text	NO
I2:I601	Expression	=AND($Q2="Open",ISNUMBER(SEARCH("Repro",$I2)))	text	NO
L2:M601	Expression	=AND($Q2="Open",$M2<Current_Week)	text	NO
J2:J601	Expression	=ISNUMBER($K2)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
J2:L125	Date	>1/01/1900
N2:N125	Date	>1/01/1900
A2:B601	Date	>1/01/1900
C2:C601	List	=Reference!$A$3:$A$7
E2:E601	List	=Reference!$A$15:$A$17
F2:F601	Whole number	between 0 and 99