Forum Discussion
Help with an excel file automation
I have attached an excel file, which contain 4 spreadsheets, of a monthly schedule in my work.
The schedule contains ‘on-call shifts’ and ‘nightshifts.
I tried to filter my name from the tables, but unfortunately, haven’t succeeded.
It will be a significant help if anyone could guide me how to do it.
I feel that there is a better and more efficient way of this repetitive task… each month to manually search for my name and then make a new google calendar event for it.
Thank you,
Amir
3 Replies
Found a nice way:
Filter formula:
=FILTER('כוננים ותורנים'!A2:O33,('כוננים ותורנים'!B2:B33="ZBEDE A")+('כוננים ותורנים'!C2:C33="ZBEDE A")+('כוננים ותורנים'!D2:D33="ZBEDE A")+('כוננים ותורנים'!E2:E33="ZBEDE A")+('כוננים ותורנים'!F2:F33="ZBEDE A")+('כוננים ותורנים'!G2:G33="ZBEDE A")+('כוננים ותורנים'!H2:H33="ZBEDE A")+('כוננים ותורנים'!I2:I33="ZBEDE A")+('כוננים ותורנים'!J2:J33="ZBEDE A")+('כוננים ותורנים'!K2:K33="ZBEDE A")+('כוננים ותורנים'!L2:L33="ZBEDE A")+('כוננים ותורנים'!M2:M33="ZBEDE A")+('כוננים ותורנים'!N2:N33="ZBEDE A")+('כוננים ותורנים'!O2:O33="ZBEDE A"))
=FILTER('השלמה והנהלה'!A2:H33,('השלמה והנהלה'!B2:B33="ZBEDE A")+('השלמה והנהלה'!C2:C33="ZBEDE A")+('השלמה והנהלה'!D2:D33="ZBEDE A")+('השלמה והנהלה'!E2:E33="ZBEDE A")+('השלמה והנהלה'!F2:F33="ZBEDE A")+('השלמה והנהלה'!G2:G33="ZBEDE A")+('השלמה והנהלה'!H2:H33="ZBEDE A"))
=FILTER(OBSTETR!A2:H33,(OBSTETR!B2:B33="ZBEDE A")+(OBSTETR!C2:C33="ZBEDE A")+(OBSTETR!D2:D33="ZBEDE A")+(OBSTETR!E2:E33="ZBEDE A")+(OBSTETR!F2:F33="ZBEDE A")+(OBSTETR!G2:G33="ZBEDE A")+(OBSTETR!H2:H33="ZBEDE A"))
and here it is with Macro:
Sub SCHEDULE()
'
' SCHEDULE Macro
'
' Keyboard Shortcut: Ctrl+t
'
Worksheets(1).Select
ActiveSheet.Name = "a"
Worksheets(2).Select
ActiveSheet.Name = "b"
Worksheets(4).Select
Sheets.Add After:=ActiveSheet
ActiveSheet.DisplayRightToLeft = True
Sheets("a").Select
Range("B2:O2").Select
Selection.Copy
Sheets("Sheet1").Select
Range("B1").Select
ActiveSheet.Paste
Range("A1").Select
ActiveCell.FormulaR1C1 = "Date"
Range("A10").Select
ActiveCell.FormulaR1C1 = "Date"
Range("I10").Select
Selection.NumberFormat = "General"
ActiveCell.FormulaR1C1 = "Date"
Range("A2:A9").Select
Selection.NumberFormat = "d"
Range("A11:A17").Select
Selection.NumberFormat = "d"
Range("I11:I17").Select
Selection.NumberFormat = "d"
Sheets("b").Select
Range("B2:H2").Select
Selection.Copy
Sheets("Sheet1").Select
Range("J10").Select
ActiveSheet.Paste
Sheets("OBSTETR").Select
Range("B2:H2").Select
Selection.Copy
Sheets("Sheet1").Select
Range("B10").Select
ActiveSheet.Paste
Range("A2").Select
ActiveCell.Formula2R1C1 = _
"=FILTER(a!RC:R[31]C[14],(a!RC[2]:R[31]C[2]=""ZBEDE A"")+(a!RC[3]:R[31]C[3]=""ZBEDE A"")+(a!RC[4]:R[31]C[4]=""ZBEDE A"")+(a!RC[5]:R[31]C[5]=""ZBEDE A"")+(a!RC[6]:R[31]C[6]=""ZBEDE A"")+(a!RC[7]:R[31]C[7]=""ZBEDE A"")+(a!RC[8]:R[31]C[8]=""ZBEDE A"")+(a!RC[9]:R[31]C[9]=""ZBEDE A"")+(a!RC[10]:R[31]C[10]=""ZBEDE A"")+(a!RC[11]:R[31]C[11]=""ZBEDE A"")+(a!RC[12]:R[31]C[12]=""ZBEDE A"")+(a!RC[13]:R[31]C[13]=""ZBEDE A"")+(a!RC[14]:R[31]C[14]=""ZBEDE A""))"
Range("A2").Select
Range("I11").Select
ActiveCell.Formula2R1C1 = _
"=FILTER(b!R[-9]C[-8]:R[22]C[-1],(b!R[-9]C[-8]:R[22]C[-8]=""ZBEDE A"")+(b!R[-9]C[-7]:R[22]C[-7]=""ZBEDE A"")+(b!R[-9]C[-6]:R[22]C[-6]=""ZBEDE A"")+(b!R[-9]C[-5]:R[22]C[-5]=""ZBEDE A"")+(b!R[-9]C[-4]:R[22]C[-4]=""ZBEDE A"")+(b!R[-9]C[-3]:R[22]C[-3]=""ZBEDE A"")+(b!R[-9]C[-2]:R[22]C[-2]=""ZBEDE A"")+(b!R[-9]C[-1]:R[22]C[-1]=""ZBEDE A""))"
Range("I11").Select
Range("A11").Select
ActiveCell.Formula2R1C1 = _
"=FILTER(OBSTETR!R[-9]C:R[22]C[7],(OBSTETR!R[-9]C[1]:R[22]C[1]=""ZBEDE A"")+(OBSTETR!R[-9]C[2]:R[22]C[2]=""ZBEDE A"")+(OBSTETR!R[-9]C[3]:R[22]C[3]=""ZBEDE A"")+(OBSTETR!R[-9]C[4]:R[22]C[4]=""ZBEDE A"")+(OBSTETR!R[-9]C[5]:R[22]C[5]=""ZBEDE A"")+(OBSTETR!R[-9]C[6]:R[22]C[6]=""ZBEDE A"")+(OBSTETR!R[-9]C[7]:R[22]C[7]=""ZBEDE A""))"
Range("A11").Select
Worksheets(5).Select
ActiveSheet.Name = "My Schedule"
End SubThanks!
- Shalom,
Enclosed your file with a filter, I am not sure whether this is what you want. But it's worth a try.
Take a look at the file, if that's okay I would love to know. If not, I would appreciate your feedback.
It would also be helpful if you inform us about the Excel version and your operating system, so that you can always receive faster and more precise help.Thank you for your understanding and patience
תודה על ההבנה והסבלנות
Nikolino
I know I don't know anything (Socrates)
- Thank you Nicolino!
Each column is for a different type of shift.
I need the excel to
1. Find my name, 'ZBEDE A'
2. Output the dates+column names.
3. Create a new event in the respective date, and column name in my Google Calendar
Thank you very much!!
