Pulling Data Into New Sheet Based Upon Cell Criteria

[welshraz]

Afternoon,

I have no idea if this is possible.....

I have a workbook with multiple sheets. Each sheet has the same columns etc., with information pertaining to different projects that are currently being worked. In column A there is a dropdown list where the user can select the current 'Status' of each aspect of the project. What I need is a macro or something that will pull some of the information from a row once the 'Status' (column A) is changed to Civils, and input it into the 'Evidence' sheet. This sheet will then have some data manually added to it based upon the data that is pulled through.

I did think about using the filter formula with some Vlookups, but this does not move the manually added data when additional rows are pulled through.

On the 'Evidence' tab, where the data will be pasted to, columns C - K and W - AA will need to be pulled from the other worksheets, and everything else will be manually input. Everything I have tried so far has either overwritten, not moved the manually added data, or doesn't work for multiple worksheets

Evidence Test.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O			R	S	T	U	V	W	X	Y	Z
2	Status	A55 Reference	Notes	Contractor	Sub Contractor	Project	Cluster	PIANOI	Siebel Reference	No. of Queries (times fed back to field)	SPO/ NASO	Type of NA	UG/OH	PIA Team Member Handling NA	Date Submitted to AB			SharePoint Date	Project Team Confirmation	Eastings/ Northings	Address	Town	Postcode	Number of blockages	Traffic Management Required	A55 Pass/Fail
3	Civils	1				Barnstaple	NF013	PIANOI100001234567	OR012-1234567891011		SPO	Blockage	UG		07/02/2020									1
4	Civils	2				Barnstaple	NF013	PIANOI100001234568	OR012-1234567891015		SPO	Blockage	UG		20/11/2020									1
5	Civils	3				Barnstaple		PIANOI100001234569	OR012-1234567891018		SPO	Blockage	UG		20/11/2020									1
6	Civils	4				Barnstaple	NF013	PIANOI100001234567	OR012-1234567891011		SPO	Blockage	UG		25/11/2020									1
7	Civils	5				Barnstaple	NF013	PIANOI100001234568	OR012-1234567891015		SPO	Blockage	UG		25/11/2020									1
8	Field Fail	6				Devon		PIANOI100001234569	OR012-1234567891018				UG
9	Field Fail	7				Devon		PIANOI100001234567	OR012-1234567891011				UG
10	Civils	8				Barnstaple	NF013	PIANOI100001234568	OR012-1234567891015		SPO	Blockage	UG		12/10/2020									1
11	Field Fail	9				Devon		PIANOI100001234569	OR012-1234567891018				OH
12	Civils	10				Bratton Fleming	NF009	PIANOI100001234567	OR012-1234567891011			D/pole	OH
13	Civils	11				Barnstaple	NF013	PIANOI100001234568	OR012-1234567891015			D/pole	OH
14	Tungsten	12				Bratton Fleming	NF009	PIANOI100001234569	OR012-1234567891018		NASO	D/pole	OH
15	Tungsten	13				Fairy Cross, Torrington	NF018	PIANOI100001234567	OR012-1234567891011		NASO	D/pole	OH		18/02/2021											Pass
16	Civils	14				Fairy Cross, Torrington	NF018	PIANOI100001234568	OR012-1234567891015		NASO	D/pole	OH		15/03/2021											Pass
17	Civils	15				Fairy Cross, Torrington	NF018	PIANOI100001234569	OR012-1234567891018		NASO	D/pole	OH		15/03/2021											Pass
18	Civils	16				Fairy Cross, Torrington	NF018	PIANOI100001234567	OR012-1234567891011		NASO	D/pole	OH		15/03/2021											Pass
19	Civils	17				Fairy Cross, Torrington	NF018	PIANOI100001234568	OR012-1234567891015		NASO	D/pole	OH		15/03/2021											Pass
Devon

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B:B	Cell Value	duplicates	text	NO
A83,A86,A37,A8:A9,A11	Expression	=$A8="Field Fail"	text	NO
A83,A86,A37,A8:A9,A11	Expression	=$A8="Civils"	text	NO
B3:AX3,C80:S80,U80:AX80,C352:AX352,C351:G351,I351:AX351,C354:AX354,C353:S353,U353:AX353,B359:AX3353,C81:AX350,C356:AX358,C70:AX79,B7:B1432,C8:G69,B4:G7,H4:AX69	Expression	=$A3="Desk"	text	NO
B3:AX3,C80:S80,U80:AX80,C352:AX352,C351:G351,I351:AX351,C354:AX354,C353:S353,U353:AX353,B359:AX3353,C81:AX350,C356:AX358,C70:AX79,B7:B1432,C8:G69,B4:G7,H4:AX69	Expression	=$A3="Invalid"	text	NO
B3:AX3,C80:S80,U80:AX80,C352:AX352,C351:G351,I351:AX351,C354:AX354,C353:S353,U353:AX353,B359:AX3353,C81:AX350,C356:AX358,C70:AX79,B7:B1432,C8:G69,B4:G7,H4:AX69	Expression	=$A3="Desk Fail"	text	NO
B3:AX3,C80:S80,U80:AX80,C352:AX352,C351:G351,I351:AX351,C354:AX354,C353:S353,U353:AX353,B359:AX3353,C81:AX350,C356:AX358,C70:AX79,B7:B1432,C8:G69,B4:G7,H4:AX69	Expression	=$A3="Field Fail"	text	NO
B3:AX3,C80:S80,U80:AX80,C352:AX352,C351:G351,I351:AX351,C354:AX354,C353:S353,U353:AX353,B359:AX3353,C81:AX350,C356:AX358,C70:AX79,B7:B1432,C8:G69,B4:G7,H4:AX69	Expression	=$A3="Civils"	text	NO

Evidence Test.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R		T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
3	SPO evidence Status	PIA Officer	A55 Status	A55 Reference	Project	Cluster	Contractor	Sub Contractor	PIANOI	Siebel Case Ref	Blockage Cost	PIANOI Expiry Date	NOI complete	NOI Completed Date	SPO evidence date requested by Project Team (historic column)	SPO evidence date requested	SPO evidence date requested	SPO Evidence received		How many requests	4 week deadline date	SPO Go Canvas/Unique Ref:	Town/cluster	Lat: Long: or E: N:	Address	Town	Postcode	Civils Complete Date	Predicted SPO Complete Date	SPO Submitted to AB date	SPO - Prework	SPO - Blockage	SPO - Repair	SPO - Resinstatement	SPO submitted to OR	SPO submitted to OR - MMM-YY	SPO Approved Date	SPO Approved Date - MMM-YY	SPO - Fail Reason	NWA Cancelled due to no SPO evidence/or no blockage	Notes
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Evidence

 

[DanteAmor]

I have some doubts:
1. The source columns go in a different column in the destination. Then the macro should adjust them.

2. The number in column A55 Reference, is it a unique identifier, that is, the number 1, does it only exist on the "Devon" sheet or can it also exist on another sheet?

3. From the answer to question 2, a couple more questions depend. For example, what if on sheet "Devon" cell A3 changes from "Civils" to "Field Fail". You have to do something on the "Evidence" sheet. And if it returns to "Civils", is a new record created or an existing record must be searched to update data?

4. From columns W to AA it is difficult to identify which will be your destination column. There I ask for your help so that you indicate, for all the columns that you want to pass, which is the origin and which is its destination.

5. Do you want this to be automatic, when a cell changes to "Civils" or do you prefer to press a button and update all the sheets in the sheet "Evidence"?
6. Or explain what your idea of the process is.

I think we could start with that. ?