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.
Thanks,
Vij
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.
- The sheets have reference ids (Auto populated).
- "Control" sheet in Column R has the condition "Transfer to Ops Team".
- If this condition is met then the whole row is copied to the "Phy Sec" sheet at the appropriate time and place.
- The Phy Sec team would also be entering data in this sheet manually.
- I am looking for the enteries to be sorted as per the date and time.
- 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.
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 ControlCell 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 | 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 | 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 | 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 | 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 | 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