Microsoft Office Forums

Go Back   Microsoft Office Forums > >
Reload this Page Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon
Register FAQ

Reply
Page 3 of 4 12 3 4
 
Thread Tools Display Modes
  #1  
Old 11-02-2018, 11:43 PM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2007
Expert
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon 		 
Join Date: May 2018
Location: Philippines
Posts: 553
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default
[quote=p45cal;135050]OK, got the triangles working. Now I'm working on what users might put in, even if they get it a bit wrong - The input error could be prevented by a validation list later on when all the codes shall have been finalized. But I guess the list would over populate the cells and might cause the processing to slow down. I don't see anything wrong with the system re-writing the error into its standard code (i.e. from LLLE to LLEL)



  • Is LE something that can happen EITHER in the morning OR in the morning on the same day? - LE refers to a learner who enrol beyond the cut-off date, in this school year, the cut off date is June 4. There is no morning or afternoon LE. A LE learner comes from the same school.
Is TI something that can happen EITHER in the morning OR in the morning on the same day? - TI refers to transfer in learners, those who come from other schools, so there is no morning or afternoon for TI.


The reason for the presence of the LE, TI, DRP, TO in the form is their effect on the total daily attendance. Say on June 4, there were 10 learners but during the month a learner came in, registered as LE on June 8, making the total daily attendance on Jun 8-31 to 11 learners.


I should be the one to apologize for providing piecemeal information that have relevance to the program.
Reply With Quote
  #2  
Old 11-03-2018, 05:23 AM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2007
Expert
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon 		 
Join Date: May 2018
Location: Philippines
Posts: 553
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default
I'm afraid there's another glitch of my own making. In order to familiarize the teachers in filling up the form, I copied it with the initial cods and distributed to the teachers to practice on. But when we ran the macro, there's an error message of
'Run-time error 1004, Application-defined or object-defined error'.<If Not Intersect(shp.TopLeftCell, RngToCheck) Is Nothing Then> This was colored yellow. According to my google search, this might be the result of copying without saving the file afterwards. How do I correct the error?
Reply With Quote
  #3  
Old 11-03-2018, 05:45 AM
p45cal's Avatar
p45cal p45cal is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 10 Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2016
Expert
  
Join Date: Apr 2014
Posts: 956
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
Default
That's quite unexpected. Could you attach the exact file itself here? If it has sensitive data in, you might be able to upload it to a file sharing site and private-message me here a password.
I'm thinking first along the lines of the code not being in a standard module, but in a sheet's or the workbook's module. The line Set RngToCheck = Range("D13:AB63") has an unqualified reference and it may not be the same as the ActiveSheet of the subsequent line.
Reply With Quote
  #4  
Old 11-03-2018, 06:35 AM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2007
Expert
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon 		 
Join Date: May 2018
Location: Philippines
Posts: 553
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default
I'm sorry, I found the culprit and it is the validation list that I placed. it's okay now that I cleared the cells of all validation lists.
Reply With Quote
  #5  
Old 11-03-2018, 06:51 AM
p45cal's Avatar
p45cal p45cal is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 10 Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2016
Expert
  
Join Date: Apr 2014
Posts: 956
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
Default
change:
Code:
    If Not Intersect(shp.TopLeftCell, RngToCheck) Is Nothing Then shp.Delete
to:
Code:
  If shp.Type <> msoFormControl Then
    If Not Intersect(shp.TopLeftCell, RngToCheck) Is Nothing Then shp.Delete
  End If
and you can keep your data validation.
Reply With Quote
  #6  
Old 11-03-2018, 07:11 AM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2007
Expert
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon 		 
Join Date: May 2018
Location: Philippines
Posts: 553
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default
Thank you! Validation list in place again to prevent typo errors.
Reply With Quote
  #7  
Old 11-03-2018, 08:02 AM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2007
Expert
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon 		 
Join Date: May 2018
Location: Philippines
Posts: 553
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default
BTW, the teachers are fully supportive of the added (creative) triangles for am, pm and am/pm cutting classes. They clarified that am or pm cutting class is equivalent to .5 or half day absent while am and pm cutting class is 1 day absent in the computation for daily attendance.


I have a attached a list of about 54 codes for every possible student behaviour affecting daily attendance. Please feel free to change or add any for clarity and consistency.


I'm really sorry about the ever changing codes. I deal with numbers daily but I don't know how the teachers keep track of the myriad movements and attendance of their students.
Attached Files
File Type: xlsx Code List.xlsx (15.5 KB, 12 views)
Last edited by Marcia; 11-04-2018 at 05:07 AM.
Reply With Quote
  #8  
Old 11-09-2018, 09:32 PM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2007
Expert
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon 		 
Join Date: May 2018
Location: Philippines
Posts: 553
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default
I edited formula in the column counting the number of absences to:


=SUM(COUNTIF(A36:AA36,{"*X*","*C*"})*0.5)+SUM(COUN TIF($D36:$AA36,{"X X","XTIX","CTIC","XLEX","CC","CLEC"})*0.5)


While in the rows counting the daily attendance, the kilometer long formula was adited to:

=IF(OR(D$12={"Sat","Sun","NC",""}),"",$C14-SUM(COUNTIF($D$15$D$42,{"*X*","X X","*C*","CC","XTIX","CTIC","XLEX","CLEC"})*0.5 )-SUM(COUNTIF($D$15:$D$42,{"T/O","DRP"}))+SUM(COUNTIF($D$15:$D$42,{"*LE*","*TI*" ,"T/I"})))
Reply With Quote
  #9  
Old 11-19-2018, 05:55 PM
p45cal's Avatar
p45cal p45cal is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 10 Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2016
Expert
  
Join Date: Apr 2014
Posts: 956
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
Default
1. Would you be happy with the macro calculating the totals of absences etc. instead of formulae on the sheet?
2. Would it be OK to change any instance of T/O entered by the user to TO? Likewise with T/I being changed to TI?
Reply With Quote
  #10  
Old 11-20-2018, 06:08 AM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2007
Expert
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon 		 
Join Date: May 2018
Location: Philippines
Posts: 553
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default
Quote:
Originally Posted by p45cal View Post
1. Would you be happy with the macro calculating the totals of absences etc. instead of formulae on the sheet?
2. Would it be OK to change any instance of T/O entered by the user to TO? Likewise with T/I being changed to TI?

1. A macro calculating the total absences is very much much better than formulae because there is less chance by the user deleting a macro than a formula, even if the formulae is sheet protected. However, would the macro be affected if say on June 12, there was no classes. Under the column of June 12, I merge the cells then overwrite the formula to Independence Day (vertical text). To prevent the macro for triangles activating the shape for cutting classes, I insert a symbol that looks like c to represent the letter c in the word independence. I do the same for no classes with reasons containing letter L, the macro code for tardy.

2. We retain the T/O and T/I because they are codes by order of the department of education. T/O for Transferred Out learners, T/I for Transferred In, DRP for Dropped, and surprise, just straight LE for Late Enrollment.
Reply With Quote
  #11  
Old 11-24-2018, 07:24 PM
p45cal's Avatar
p45cal p45cal is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 10 Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2016
Expert
  
Join Date: Apr 2014
Posts: 956
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
Default
See attached. It's work in progress.
On sheet SF 2 JUNE HARD COPY, two buttons in the vicinity of cell AT9.
Update triangles will add triangles
Reset will take them away and show all text so that users can comfortably edit them.
What Update triangles does is:
Takes each row in the range E16:AI66
then takes each cell in that row then
will only look at cells having a diagonal line, then it will set the font colour to white, then, only if the cell does NOT have a formula in it:
it will look at the value in the cell, take out forward slashes, remove all trailing spaces and all but one leading space, convert multiple spaces to a single space and then look up what's left in a dictionary of 100 or so possibilities. That dictionary contains the definitions; things such as Absent AM, Late AM, Cutting AM, Absent PM, Late PM, Cutting PM, which will be TRUE if they apply. There's also a column (rightmost) containing a standardised version of the string in the cell which will replace what's in the cell if it's different from that standard version. Having found the entry in the dictionary, it then goes about adding coloured triangles, red for cutting classes and blue for lateness. If there are both in one morning or one afternoon, the cutting classes triangle is halved in size and placed on top of the late triangle. Then if there are any absences (Xs) any X in the cell's text is made visible again, made super- or sub-script to place it comfortably in the cell. If the dictionary lookup doesn't find anything, that cell is coloured yellow (so that you can experiment and find combinations which should be found in the dictionary - tell me what they are and I'll add them).
When each row has been processed, cells in columns AK and AL are updated.


Try it.
I will speed it up later.



You can get an idea of what's in the dictionary by running the macro PrintTable, which will add a new sheet. Look-up values are in the left column.


The general protocol for entering text in the cells is that a space separates am from pm data, as does TI and LE which replace the space (because TI and LE don't have a morning/afternoon attribute). Otherwise the order in which L, C and X appear shouldn't matter. Where you have the likes of LL, it's assumed, because you can't be late twice in the morning, that one L is for the morning, and the other for the afternoon. The same applies to XX etc.
Attached Files
File Type: xlsm msofficeforums40597JUNE 2018 QUERYBoffer01.xlsm (133.6 KB, 10 views)
Last edited by p45cal; 11-25-2018 at 04:20 AM.
Reply With Quote
  #12  
Old 11-25-2018, 02:49 AM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2007
Expert
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon 		 
Join Date: May 2018
Location: Philippines
Posts: 553
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default
Big thanks for this cal, but the file is protected.


Got it. I'll get back to you soonest.
Reply With Quote
  #13  
Old 11-25-2018, 06:17 AM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2007
Expert
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon 		 
Join Date: May 2018
Location: Philippines
Posts: 553
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default
This is brilliant Cal, impressive. It is a far cry from the usual method of shading the shapes with crayons and calculating the daily learners movement with a calculator. My teacher friends will feel they have travelled to the moon and back once they got the hang of preparing their monthly reports through this automated system. I'v tried all the possible scenarios and I think everything is covered by the dictionary. The built in auto correction of any possible erroneous entry is what makes the system more amazing. Thank you.


A bit of correction in the formulae in AK and AL. Each instance of tardy is counted as 1 not .5, so AM tardy is 1, PM tardy is 1, AM and PM tardy is 2, while cutting class is counted as .5 like absences, so AM cutting class is .5, PM is .5, AM and PM is 1. cutting class is added to absences in AK. I'm sorry about this.
Reply With Quote
  #14  
Old 11-25-2018, 06:49 AM
p45cal's Avatar
p45cal p45cal is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 10 Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2016
Expert
  
Join Date: Apr 2014
Posts: 956
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
Default
Quote:
Originally Posted by Marcia View Post
A bit of correction in the formulae in AK and AL. Each instance of tardy is counted as 1 not .5, so AM tardy is 1, PM tardy is 1, AM and PM tardy is 2, while cutting class is counted as .5 like absences, so AM cutting class is .5, PM is .5, AM and PM is 1. cutting class is added to absences in AK. I'm sorry about this.
Try changing the EmbellishCell macro to:
Code:
Sub EmbellishCell(cll, x, z, Absences, Lates)
CuttingSizeAM = 1: CuttingSizePM = 1
If x(Application.Match("Absent AM", z, 0) - 1) = True Then Absences = Absences + 0.5
If x(Application.Match("Absent PM", z, 0) - 1) = True Then Absences = Absences + 0.5
If x(Application.Match("Late AM", z, 0) - 1) = True Then
  AddTriangle cll, "AM", "LATE", 1
  Lates = Lates + 1
  CuttingSizeAM = 0.5
End If
If x(Application.Match("Late PM", z, 0) - 1) = True Then
  AddTriangle cll, "PM", "LATE", 1
  Lates = Lates + 1
  CuttingSizePM = 0.5
End If
If x(Application.Match("Cutting AM", z, 0) - 1) = True Then
  AddTriangle cll, "AM", "CUTTING", CuttingSizeAM
  Absences = Absences + 0.5
End If
If x(Application.Match("Cutting PM", z, 0) - 1) = True Then
  AddTriangle cll, "PM", "CUTTING", CuttingSizePM
  Absences = Absences + 0.5
End If
End Sub
Reply With Quote
  #15  
Old 11-25-2018, 06:54 PM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2007
Expert
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon 		 
Join Date: May 2018
Location: Philippines
Posts: 553
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default
Thank you again Cal. I feel guilty monopolizing your time in this forum. this thread had gone 4 pages already. I can't thank you enough so God bless you. I created a userform for data entry and I am now trawling the web for codes that would make the command buttons run.
Reply With Quote
Reply
Page 3 of 4 12 3 4

« Previous Thread | Next Thread »


Similar Threads
Thread Thread Starter Forum Replies Last Post
New learner need help - convert tables in word format to ppt slides. sas Word VBA 5 09-21-2017 08:33 PM
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Good morning/afternoon/evening based on time dwirony Word VBA 1 01-03-2017 01:01 PM
Need conditional formatting formula namedujour Excel 1 05-27-2016 08:58 AM
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Conditional Formatting Formula teza2k06 Excel 1 08-22-2014 02:49 AM
Conditional Formula UICOMP Excel 1 05-18-2012 12:59 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:20 PM.

Contact Us - Privacy Statement - Top

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft