How to update a list tab based on an evolving data tab

[runderwood10]

Hi Everyone,

I have a set of data split into 2 tabs; List and Data.

I would like The List tab to take information from the Data tab, it will also include additional columns of data which will be manually populated. i.e. columns A - E will take data from the Data tab, and columns F - U are manually populated.

The Data tab will be populated using an externally ran report, which will contain new rows of data throughout the year. This external report will be ran periodically, and will be pasted into the Data tab, overwriting what was in there previously.

The List tab would then search for new rows of data on the Data tab and insert them, shifting the entire row down and not overwriting columns F - U. The new rows of data may not be at the bottom of the sheet though; they may appear on any row.

Here is an example of the List tab:

TEST V4.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Centre	Faculty	AO	Title	Aim Code	Column 1	Column 2	Column 3	Column 4	Column 5	Column 6	Column 7	Column 8	Column 9	Column 10	Column 11	Column 12	Column 13	Column 14	Column 15	Column 16
2	Centre 01	Area 01	AO1	Title 01	X1	TEST
3	Centre 01	Area 01	AO2	Title 02	X2	TEST
4	Centre 01	Area 01	AO3	Title 03	X3	TEST
5	Centre 01	Area 01	AO4	Title 04	X4	TEST
6	Centre 02	Area 02	AO1	Title 10	X10	TEST
7	Centre 01	Area 01	AO5	Title 05	X5
8	Centre 01	Area 01	AO6	Title 06	X6
9	Centre 01	Area 01	AO7	Title 07	X7	TEST
10	Centre 01	Area 01	AO8	Title 08	X8
11	Centre 01	Area 01	AO9	Title 09	X9
List

Cell Formulas
Range	Formula
A2:A11	A2	=IFERROR(INDEX(Data!$A$2:$A$10001,AGGREGATE(15,6,(ROW($A$1:$A$10000))/(Data!$A$1:$A$10001<>""),ROWS($1:1))),"")
B2:B11	B2	=IFERROR(INDEX(Data!$B$2:$B$10001,AGGREGATE(15,6,(ROW($A$1:$A$10000))/(Data!$A$2:$A$10001<>""),ROWS($1:1))),"")
C2:C11	C2	=IFERROR(INDEX(Data!$M$2:$M$10001,AGGREGATE(15,6,(ROW($A$1:$A$10000))/(Data!$A$2:$A$10001<>""),ROWS($1:1))),"")
D2:D11	D2	=IFERROR(INDEX(Data!$H$2:$H$10001,AGGREGATE(15,6,(ROW($A$1:$A$10000))/(Data!$A$2:$A$10001<>""),ROWS($1:1))),"")
E2:E11	E2	=IFERROR(INDEX(Data!$N$2:$N$10001,AGGREGATE(15,6,(ROW($A$1:$A$10000))/(Data!$A$2:$A$12001<>""),ROWS($1:1))),"")

Named Ranges
Name	Refers To	Cells
Data!_FilterDatabase	=Data!$A$1:$AR$9952	A2:A11

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
1:11	Expression	=$F1=""	text	NO

And here is an example of the Data tab:

TEST V4.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Centre	Area	Team	PARENT2	CRS_CODE	TOTAL_PLACES	ActiveEnrol	CRS_TITLE	STATUS	MAIN_COURSE2	MOA	UnitCategory	awarding_body	LearningAim	NVQLevel	FL2
2	Centre 01	Area 01	Team 01	001	001			Title 01	ACTIVE	Q1		F1	AO1	X1	1
3	Centre 01	Area 01	Team 01	001	002			Title 02	ACTIVE	Q1		F1	AO2	X2
4	Centre 01	Area 01	Team 01	001	003			Title 03	ACTIVE	Q1		F1	AO3	X3	X
5	Centre 01	Area 01	Team 01	001	004			Title 04	ACTIVE	Q1		F1	AO4	X4	E
6	Centre 02	Area 02	Team 02	001	010			Title 10	ACTIVE	Q1		F1	AO1	X10
7	Centre 01	Area 01	Team 01	001	005			Title 05	ACTIVE	Q1		F1	AO5	X5	E
8	Centre 01	Area 01	Team 01	001	006			Title 06	ACTIVE	Q1		F1	AO6	X6	E
9	Centre 01	Area 01	Team 01	001	007			Title 07	ACTIVE	Q1		F1	AO7	X7	1
10	Centre 01	Area 01	Team 01	001	008			Title 08	ACTIVE	Q1		F1	AO8	X8	2
11	Centre 01	Area 01	Team 01	001	009			Title 09	ACTIVE	Q1		F1	AO9	X9	E
Data

Any help or advice would be greatly appreciated!

Thanks so much in advance
R