Insert data into a second sheet based on a condition

[vij]

Hi,

I have two sheets in which I enter data. In both the sheets based on certain condition I want the data to be inserted and sorted date and time wise.

1. The sheets have reference ids (Auto populated).
2. "Control" sheet in Column R has the condition "Transfer to Ops Team".
3. If this condition is met then the whole row is copied to the "Phy Sec" sheet at the appropriate time and place.
4. The Phy Sec team would also be entering data in this sheet manually.
5. I am looking for the enteries to be sorted as per the date and time.
6. Similarly if "Transferred to Control" is enetred in the "Phy Sec" sheet then the row is copied to the Control sheet and sorted as per the date and time.
7. 
   

8. Combined Task Tracker Macro Enabled Trial.xlsm
   	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
   3	Reference Id	Site	RequestMode	Date	Time	Operator	Request	Assistance	Requestor	EmployeeType	LDAP	BU	Department	Company	Contact	Scope
   4	N132-0000001	N132	Mail	7-Sep-20	10:33 AM	Udit	Badge	Change/modify Badge	Radhakrishnan Nair A V	Adobe Paid Temp	nair@adobe.com	GSS	GSS	Adobe		Badge was activated
   5	NOS-0000001	NOS	Mail	7-Sep-20	12:00 PM	Vanjeet	OnsiteAssistance	exit formalties	Utsav Mazumdar	Regular	umazumda@adobe.com	Employee Experience	Global Employee Resource Center	Adobe		Transferred to GSO Team
   6	N132-0000002	N132	Walky	7-Sep-20	12:12 PM	Vanjeet	Miscellaneous	Maintenance Query	Anmol Sharma	Vendor	cus60415@adobe.com	Employee Experience	GSO	C&W		Transferred to Sec Gate 2
   7	PTP-0000001	PTP	BoardLineCall	7-Sep-20	12:46 PM	Udit	OffsiteAssistance	Material Enquiry	Robin Chibbar	Adobe Paid Temp	chibber@adobe.com	IT & Cloud Operations	Employee	Adobe	8884085854	Return of CISCO Material
   8	N132-0000003	N132	Mail	7-Sep-20	1:02 PM	Vanjeet	OnsiteAssistance	IT Related Query	Vipin Sachdeva	Regular	vipin@adobe.com	IT & Cloud Operations	Hosting, Cloud and Engineering Services	Adobe		Request was completed by ISOC
   9	N132-0000004	N132	Mail	7-Sep-20	2:09 AM	Vanjeet	Open	Opening access area in absence of the owner	Vishesh Gupta	Regular	vishegup@adobe.com	Customer Experience	Customer Experience	Adobe		Request was completed by ISOC
   10	N132-0000005	N132	Mail	7-Sep-20	2:12 AM	Ajay	GSSQuery	DSR Related Query	Vinay Shahi	Vendor	wal57161@adobe.com	Employee Experience	GSS	Securitas	35782	Request was completed by ISOC
   11	N132-0000006	N132	Mail	7-Sep-20	4:02 PM	Amit	OnsiteAssistance	exit formalties	Astha Kabra	Regular	astkabra@adobe.com	Creative Product	Adobe Design	Adobe		Transferred to ERC Team
   12	N132-0000007	N132	Mail	8-Sep-20	12:18 AM	Ajay	OnsiteAssistance	IT Related Query	Sucharita Ghosh	Regular	sucghosh@adobe.com	WWFO	Adobe Customer Solutions	Adobe		Transferred to Ops Team
   13	N132-0000008	N132	ExtensionCall	8-Sep-20	1:23 AM	Ajay	GSSQuery	Vendor related	Vinay Shahi	Vendor	wal57161@adobe.com	Employee Experience	GSS	Securitas	35782	Query was resolved
   14	N132-0000009	N132	ExtensionCall	8-Sep-20	1.29 AM	Ajay	GSSQuery	DSR Related Query	Vinay Shahi	Vendor	wal57161@adobe.com	Employee Experience	GSS	Securitas	35782	Query was resolved
   15	N132-0000010	N132	Mail	8-Sep-20	8:58 AM	Swati	Open	Tech Café	Sreejesh	Vendor	cus60285@adobe.com	Employee Experience	GSO	C&W		Transferred to Ops Team
   16	N132-0000011	N132	Mail	8-Sep-20	10:10 AM	Vanjeet	OnsiteAssistance	IT Related Query	Mrinalini Sardar	Regular	sardar@adobe.com	Creative Product	Adobe Design	Adobe		Transferred to Ops Team
   17	N132-0000012	N132	Mail	8-Sep-20	10:57 AM	Swati	Open	Shipment Room	Sreejesh	Vendor	cus60285@adobe.com	Employee Experience	GSO	C&W		Transferred to Ops Team
   18	N132-0000013	N132	Walky	8-Sep-20	11:29 AM	Vanjeet	GSSQuery	IT Related Query	Raju Pandey	Vendor	wal85634@adobe.com	Employee Experience	GSS	Securitas	Walky	Query was resolved
   19	N132-0000014	N132	Walky	8-Sep-20	12:02 PM	Vanjeet	GSSQuery	Material Enquiry	Raju Pandey	Vendor	wal85634@adobe.com	Employee Experience	GSS	Securitas	Walky	Query was resolved
   20	N132-0000015	N132	Mail	8-Sep-20	12:35 PM	Vanjeet	OnsiteAssistance	IT Related Query	Ashish Mishra	Regular	ashishm@adobe.com	Creative Product	Core Technologies & Products	Adobe		Transferred to Ops Team
   Control

   Cell Formulas
   Range	Formula
   C4:C20	C4	=D4&TEXT(COUNTIF($D$4:D4,D4),"-0000000")

   Cells with Conditional Formatting
   Cell	Condition		Cell Format	Stop If True
   R18	Expression	=AND($P25<>"",($P25=VB))	text	NO
   R18	Expression	=AND($O25<>"",($O25=VB))	text	NO
   L19	Expression	=AND($T24<>"",($T24=VB))	text	NO
   L18	Expression	=AND($T23<>"",($T23=VB))	text	NO
   P10	Expression	=AND($T17<>"",($T17=VB))	text	NO
   L10	Expression	=AND($T15<>"",($T15=VB))	text	NO
   I10	Expression	=AND($T17<>"",($T17=VB))	text	NO
   M10	Expression	=AND($S17<>"",($S17=VB))	text	NO
   L14	Expression	=AND($T19<>"",($T19=VB))	text	NO
   L13	Expression	=AND($T18<>"",($T18=VB))	text	NO
   L7	Expression	=AND($Q12<>"",($Q12=VB))	text	NO
   O7	Expression	=AND($T12<>"",($T12=VB))	text	NO
   O6	Expression	=AND($T11<>"",($T11=VB))	text	NO
   L6	Expression	=AND($T11<>"",($T11=VB))	text	NO
   R13:R14,R19	Expression	=AND($P20<>"",($P20=VB))	text	NO
   Q13:S14,R19,R77	Expression	=AND($O20<>"",($O20=VB))	text	NO
   R20	Expression	=AND($P24<>"",($P24=VB))	text	NO
   R20	Expression	=AND($O24<>"",($O24=VB))	text	NO
   P20	Expression	=AND($T24<>"",($T24=VB))	text	NO
   R21,R16:R17,R22:S22,Q11:S12,Q15:S15	Expression	=AND($O16<>"",($O16=VB))	text	NO
   N15:P15,I16,I15:L15,I17:L17,P16:P17,N17:O17,I21,P21,K21:L21,K16:M16,I22:K22,O22:P22,C40,C30:C34,G71:H71,E71,C71,C76:C78,E76:E78,G76:H78,C82,G82:H82,G116:G117,E116:E117,C116:C117,P109,I4:K4,P7,M11,I11:K11,P11:P12,I12,K12:N12	Expression	=AND($T9<>"",($T9=VB))	text	NO
   R7,R11:R12,R15:R17,R21	Expression	=AND($P12<>"",($P12=VB))	text	NO
   L4	Expression	=AND($Q9<>"",($Q9=VB))	text	NO
   Q31,S29:S34,Q20:Q21	Expression	=AND($O38<>"",($O38=VB))	text	NO
   M8,G26,G27:H29,G35:H35,I39:J40,G41:H42,G31:H32,M72,I72:K72,I76:I77,O83,M83,J114,I115:I116,K115,I123:J124	Expression	=AND($S12<>"",($S12=VB))	text	NO
   G5:G10,E5:E14	Expression	=AND(#REF!<>"",(#REF!=VB))	text	NO
   R4	Expression	=AND($Q9<>"",($Q9=VB))	text	NO
   G17	Expression	=AND($T30<>"",($T30=VB))	text	NO
   E4:E20	Expression	=#REF!="VB"	text	NO
   E4:G4,E5:F16	Expression	=AND(#REF!<>"",(#REF!=VB))	text	NO
   O4:P4	Expression	=AND($T9<>"",($T9=VB))	text	NO
   R4	Expression	=AND($O9<>"",($O9=VB))	text	NO
   E17:F19	Expression	=AND($Q4<>"",($Q4=VB))	text	NO
   E15:E16,E39:E42,F82:F86	Expression	=AND($Q4<>"",($Q4=VB))	text	NO
   R5:R6	Expression	=AND($P11<>"",($P11=VB))	text	NO
   I7	Expression	=AND($T12<>"",($T12=VB))	text	NO
   Q7:R7	Expression	=AND($O12<>"",($O12=VB))	text	NO
   I8	Expression	=AND($T12<>"",($T12=VB))	text	NO
   Q8:S8	Expression	=AND($O12<>"",($O12=VB))	text	NO
   E17:E19,E26,E38:E41,F76:F78	Expression	=AND($Q8<>"",($Q8=VB))	text	NO
   P5:P6,I5:J5,M9,P9,I9:K9,I18,O18:P18,I6,K18,C36:C38,C41,C74:C75,E74:E75,G74:H75,E83:E86,G83:H86,C83:C86	Expression	=AND($T11<>"",($T11=VB))	text	NO
   Q9:S10,Q5:S6,R76	Expression	=AND($O11<>"",($O11=VB))	text	NO
   M9,G11,G15:G16,G20:H21,G36:H38,K40,M40,G40:H40,I75,I73:K73,J117,M65	Expression	=AND($S15<>"",($S15=VB))	text	NO
   M11:M12,M16,G25,G24:H24,M37:M38,I37:K38,G39:H39,I41:J41,M74:M75,O74:O75,I74:J75,O84,M84,I85,I113:I114,I117,J115	Expression	=AND($S16<>"",($S16=VB))	text	NO
   Q16:Q19,S16:S19	Expression	=AND($O36<>"",($O36=VB))	text	NO
   E20:F20,F112:F122	Expression	=AND($Q5<>"",($Q5=VB))	text	NO
   E20	Expression	=AND($Q9<>"",($Q9=VB))	text	NO
   G12:G14,G18:G19	Expression	=AND($S20<>"",($S20=VB))	text	NO
   P13:P14,O19:P19,I19,I13:I14,K13:K14,M13:M14,K19,P54,E72:E73,E75,G75:H75,C72:C73,C75,G72:H73,P110,K10,M10	Expression	=AND($T17<>"",($T17=VB))	text	NO
   G17,M13:M14,M67	Expression	=AND($S20<>"",($S20=VB))	text	NO
   G18:G19	Expression	=AND($T31<>"",($T31=VB))	text	NO
   C134:H1048576,1:3,M36,T46:XFD46,M43,T42:XFD42,D32:D34,T59:XFD61,D73,D76:D78,D84:D87,T1048574:XFD1048576,D123:D124,M106:M108,D106:D119,D43:D56	Expression	=AND($R4<>"",($R4=VB))	text	NO
   C5:D15	Expression	=AND($N1048573<>"",($N1048573=VB))	text	NO
   E45,G45:H45,P42,C43:C45,C20:C29,C35,G52:G55,C52:C55,E52:E55,C70,G70:H70,G87:H88,C87:C88,E87:E88,E113:E115,C113:C115,G113:H115,K121:L121,I20,K20:L20,P24,I24:J24,K25,M25,I25,M8,P8,K8	Expression	=AND($T12<>"",($T12=VB))	text	NO
   C4:D14,D15:D122	Expression	=$R4="VB"	text	NO
   C20:C25	Expression	=$R11="VB"	text	NO
   M47,M49:M50,D15:D31,D35:D37,M56:M57,M62,M64,M69,D72,D74,D85:D88,D125:D132,M103:M105,D101:D118,D42:D59,C4:D14	Expression	=AND($R6<>"",($R6=VB))	text	NO
   C17:D19,D20:D34,D41:D56	Expression	=AND($R11<>"",($R11=VB))	text	NO
   C15:D19,D20:D34	Expression	=$R5="VB"	text	NO
   C16:D16,D17:D34,D43:D56	Expression	=AND($R8<>"",($R8=VB))	text	NO
   D16:D34	Expression	=AND($P8<>"",($P8=VB))	text	NO

   Cells with Data Validation
   Cell	Allow	Criteria
   J4:J5	List	=INDIRECT(I4)
   L6:L7	List	=Employee
   L12:L20	List	=Employee
   P4:P20	List	=Company
   O15,O17:O19,O6:O7,O4,O22,O26:O27,N44:O44,N38:O38,N46:O47,N33:O33,N52:O53,N60:O60,O63,N64:O64,N71:O71,N75:O75,N80:O80,N92:O92,N94:O94,O114,O121:O122,N116:O116,N124:O124,N129:O129	List	=Department
   O17:O19	List	=Department
   O6:O7	List	=Department
   D4:D132	List	=Site
   E4:E131	List	=RequestMode

Combined Task Tracker Macro Enabled Trial.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Ops Id	Ops Site	Reference Id	Site	RequestMode	Date	Time	Operator	Request	Assistance	Requestor	EmployeeType	LDAP	BU	Department	Company	Contact	Scope	Remarks	Remarks
2	N25A-00001	N25A	N132-0000007	N132	Mail	44082	0.0125	Ajay	OnsiteAssistance	IT Related Query	Sucharita Ghosh	Regular	sucghosh@adobe.com	WWFO	Adobe Customer Solutions	Adobe	0	Transferred to Ops Team	Personal belongings collection
3	N132-00001	N132	N132-0000010	N132	Mail	44082	0.373611	Swati	Open	Tech Café	Sreejesh	Vendor	cus60285@adobe.com	Employee Experience	GSO	C&W	0	Transferred to Ops Team	Techcafe open
4	-00000		N132-0000011	N132	Mail	44082	0.423611	Vanjeet	OnsiteAssistance	IT Related Query	Mrinalini Sardar	Regular	sardar@adobe.com	Creative Product	Adobe Design	Adobe	0	Transferred to Ops Team	Personal belongings collection
5	-00000		N132-0000012	N132	Mail	44082	0.45625	Swati	Open	Shipment Room	Sreejesh	Vendor	cus60285@adobe.com	Employee Experience	GSO	C&W	0	Transferred to Ops Team	Shipment open
6	-00000		N132-0000015	N132	Mail	44082	0.524306	Vanjeet	OnsiteAssistance	IT Related Query	Ashish Mishra	Regular	ashishm@adobe.com	Creative Product	Core Technologies & Products	Adobe	0	Transferred to Ops Team	Personal belongings collection
Phy Security

Cell Formulas
Range	Formula
C2:S6	C2	=FILTER(Control!C:S,Control!R:R="Transferred to Ops team")
A2:A6	A2	=B2&TEXT(COUNTIF($B$2:B2,B2),"-00000")
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:S1	Expression	=AND($P4<>"",($P4=VB))	text	NO
F2:F1048576	Cell Value	between 778000 and 779000	text	NO

Thanks,
Vij

1. 
   

 

[vij]

Is there a way of doing this?