Posts by PantherPride

  • $80 USD: Updating a schedule using multiple criteria: time/date calcs and booleans

    Re: $80 USD: Updating a schedule using multiple criteria: time/date calcs and boolean


    Hi GCExcel,


    Another good insight. Typically, yes, all lessons are done for one category (a unit of study) before moving on -- however, I'm now envisioning one or two exceptions. Bummer.


    So, here would be 2 ways around that. The most preferable is for me to just number my lessons, like 1, 2, 3...156, 157, 158... where the numbers would transcend the categories. Therefore, if I want to move some special reading or writing assignment, say, originally marked as lesson# 88, after another lesson, say 24, then I could change 88 to a 25, and then all other lessons past lesson 24 would be renumbered, and the value for the DaysHours would cause the DateLesson to be updated.


    The other way would be to separate all categories on separate sheets. But, I think I like the above method better. Probably harder to code though...

  • $80 USD: Updating a schedule using multiple criteria: time/date calcs and booleans

    Re: $80 USD: Updating a schedule using multiple criteria: time/date calcs and boolean


    Thanks GCExcel for your questions/comments.


    Thanks S M C for your consideration.
    One way to make it easier is to just put each GradeLevel on a separate sheet. So, just work with one GradeLevel and one starting date as an example. And, focus on just one of the GradeLevels that meet every day, like HS.


    Another way to make this easier will be to drop the ability to have lessons that have 0.5 in the Days/Hours column. Each row will at least have a value of 1 for one period or 1 day. Keep in mind the next point though that there will be days marked in the Calendar that the school is not in session.


    When inserting the rows from the calendar (CAL in the GradeLevel column), the date code should look at the rows that have CAL and a value of "N" in the InSession column. This means that the school or grade level is not in session. Therefore, there will by default be no lesson for that day, and the date for the next lesson should jump the number of days marked in the DaysHours column.


    I hope this helps.
    Thanks again.

  • $80 USD: Updating a schedule using multiple criteria: time/date calcs and booleans

    Re: $80 USD: Updating a schedule using multiple criteria: time/date calcs and boolean


    Ahhh, Rats! - Good point. I forgot to fully plan this part out. Sorry. This is probably going to be too hard...


    Only these classes will meet every day:
    HS, 8G, WD, CP


    If this entire project could just focus on these 4 classes that meet every day, that would be just fine. We can do something different with the classes below:


    These are the ones that will meet twice a week for an entire year:
    3G, 4G, 5G (these are split up, like Mon and Fri, Tues and Thurs., etc.)


    These are the ones that will meet twice a week, and alternate every other Friday (and I don't have the full schedule yet in terms of which classes start on which week.
    6G, 7G

  • $80 USD: Updating a schedule using multiple criteria: time/date calcs and booleans

    Re: $80 USD: Updating a schedule using multiple criteria: time/date calcs and boolean


    Hi GCExcel, thanks for responding. Here are some answers:


    - How many lessons / day?
    Typically 1 lesson per day for each GradeLevel. However, there may be two adjacent rows that have a value of 0.5 in the DaysHours column. So, there could be two consecutive lessons for a GradeLevel. I don't think I'll allow any decimal values that aren't 0.5.


    - How many lessons / week ?
    This could vary per GradeLevel. A GradeLevel like HS may have five 1-period (1 in the DaysHours column) lessons, such as Lesson# 6, 7, 8, 9 and 10. However, another GradeLevel, such as 4G, may have ten lessons that have 0.5 in the DaysHours column, and spanning Lesson#s 11, 12, 13, 14, 15, 16, 17, 18, 19 and 10.


    - If you have 2 lessons on the same but with different start date, does it mean the week number is different?
    This could be tricky. Some classes, such as HS and 4G, are all-year classes, and their start date is basically 8/21. Other classes are only a quarter or semester long, and their start dates are listed in C1:C7. So, week numbers (starting on a Sunday) should be different for classes that have different starting dates. However, this code is the least of my worries at this point.


    - How to determine which lessons has priority over an other one? I guess all lesson no1, should be first, then is it based on the Grade Level or the Category or ???
    Priority is given in this order - grouped by GradeLevel, then by Category, then by Lesson#.


    - DaysHours values represent #hours / lesson or #days/lesson?
    Sorry about this. I'm trying to write lessons such that one lesson (row) pretty much takes up 1 period, which is an hour, which in essence, is one day (to advance the next lesson to the next day's date). So a 1 means one period or one day. A 2 means 2 periods or 2 days. However, some lessons may have a 0.5. This would be a half a period. There could be 2 lessons combined in 1 period.
    This part could probably be played with, but if a lesson goes over 1 period, then I won't allow 1.5 or 2.5--it'll just be 1 or 2, to make the date calculation advance properly.

  • $80 USD: Updating a schedule using multiple criteria: time/date calcs and booleans

    Hi Programmers (USD $80),

    Here's the scenario...As a teacher (me), when a class period's lesson takes longer than expected, it throws my entire schedule off, causing a lot of manual changes--a chain reaction really. I'd like a sheet that can automatically change the days when I happen to increase the time (the "DaysHours" value) required for a lesson. This would cause the dates for all the other lessons for that class to advance by the value entered in the "DaysHours" cell.
    Also, in a particular week, I may wish to reorder lessons for a class. So, the sheet should also be able to auto-renumber a "Lesson#" column. And, the entire sheet could resort.
    In short, I'd like help in coding a sheet to auto schedule the dates of lessons based on various columns, such as lesson order number, grade levels, class categories, and amount of time needed for a lesson.

    A single lesson is a row.

    DESIRED FUNCTIONS ON CHANGE OR MACRO:
    1. If Lesson# is changed, then Lesson# should resort ascending but grouped by Category.
    2. If Lesson# is changed (and is the same as another GradeLevel - Category - Lesson# combo), then increment the Lesson#s below accordingly (there can't be 2 Lesson#s that are identical in the same GradeLevel - Category - Lesson# combo).
    3. DaysHours should advance DateLesson by the number of days (= whole class periods), but not advance DateLesson if the value is < 1, such as .25 or 0.5. In fact, if DaysHours = 0.5, then that means that 2 lessons can occur on the same Day—so there should be 2 of the same date values if there are 2 adjacent lessons that have DaysHours = 0.5. NOTE: fractional values should only be 0.25, 0.5, 0.75).
    3. DateLesson should recalculate based on DaysHours.
    4. WeekNumber should recalculate based on the combination of C1:C7, GradeLevel, and DateLesson.
    5. WeekDay should recalculate based on DateLesson.
    6. A final sort, or a separate macro, should be based on: DateLesson, GradeLevel, Lesson#

    CONDITIONS:
    Note that I'm mixing the schoo's calendar into my lesson schedule. So, if Category = CAL (for a calendar row) and InSession = N, then skip that row's date from the date calculations, jumping to the next school day. If InSession = Y, then include the CAL row's date in date calculations (lessons can be taught on these days).

    ASSUMPTIONS:
    Lesson# for a Category does not reset to 1 at each quarter.

    SORTING & FILTERING SHOULD BE ALLOWED:
    --GradeLevel
    --Category
    --Default sort (DateLesson, GradeLevel, Lesson#)

    HERE ARE THE COLUMNS (SEE ATTACHED WKBOOK):
    GradeLevel | Category | InSession | Quarter | DateOfEvent | WeekNumber | WeekDay | DaysHours| Title | Lesson# | ... columns extend out

    DEFINITIONS:
    Grade: manually selected in a vlookup, the options are 3G, 4G, 5G, and so on.

    Category: manually selected in a vlookup, the options are CAL, KB, MATH, CC, and so on. These are different school classes.

    InSession: either Y or N, manually inputted on the Calendar sheet. If school is Not in session, then omit the date from date calculations, skipping to the next day that has a "Y"

    Quarter: there are 4 quarters in a school year.


    Note: some classes (C1:C7 in the attached sample) start in different quarters. Starting dates of the quarters are in the calendar.


    The starting dates for different GradeLevels are manually entered in:
    C1 - starting date for all grades but the following:
    C2 - starting date for grade ES
    C3 - starting date for grade 6G
    C4 - starting date for grade 7G
    C5 - starting date for grade 8G
    C6 - starting date for grade WD
    C7 - starting date for grade CP


    I would really appreciate your help. If more $$ is necessary, please let me know, and I'll try to accommodate. I can't go too much higher though. Thanks.


    E

  • USD $30 - Allow users to exclude assignment columns in total grade calculations

    Hi Developers - Attached is a gradebook with weighted categories. What I'd like to have happen is when any cell in row 7 with "inc / exc" is double-clicked, then the scores in that column would be "included" OR "excluded" in the grade calculations. Or, if the value in row 7 = "included" then the column will be included in calculations. If the value in row 7 = "excluded", then the column will be excluded in calculations. All excluded columns need to be visible to the user, such as grayed out, or the cell in row 7 should be colored red or something.


    So, I was thinking that more complex IF statements will need to used in the calculation columns. But this is starting to make my brain ache.



    BACKGROUND INFO:
    Use the "Period 2" sheet for the example.



    The categories and weights are in C2:D9.



    Participation (ABC) calculations occupy cols G:S
    Assignments & homework calcs occupy cols T:V
    Project calcs occupy cols W:Y
    Exam calcs occupy cols Z:AB
    Quiz calcs occupy cols AC:AE



    The orange colored cols are the % totals and weighted totals. Other calcs are in gray.



    The assignment/project/exam/quiz columns currently start at AK, are identified with the value *AssignCol* in row 12, and grow to the right as new assignments/projects/exams/etc. are added.



    The participation (ABC) scores start to the right of all the yellow *AssignCol* columns, are identified by a date in row 12, and combine daily attendance, behavior, and in-class participation values.


    Let me know if you need more info. I will be out for a couple of hours.
    Thanks!
    forum.ozgrid.com/index.php?attachment/54945/

  • Making hyperlinking between 2 cells in 2 sheets user-friendly, USD $20

    Hi there - I would like code that creates or simulates a hyperlink between a cell one one worksheet and a cell on another wksheet in the same wkbook. The hyperlink needs to go both ways. The hyperlinked cells should be underlined or color formatted, making users aware of the link.



    The link should be instantiated by a command in the right-click menu (right-clicking the first cell to be linked), or a triple-click of the first cell (this may be quicker than a right-click, menu item). The code then would "wait" until the second cell to be linked back to the first cell is selected somehow--such as with a double-click. The code should allow users to click sheet tabs, arrow keys, etc. in order to navigate to the second cell to be linked. A double-click or right-click on the second cell should end the linking process and code.



    Clicking a linked cell on either sheet will take the user to the linked cell in the other sheet.



    I've read about storing the first cell's (active cell's) reference as a variable, but don't know how to capture the ref of the second cell to be linked.


    Thanks!



    Here are some threads I was exploring:



    http://www.ozgrid.com/VBA/right-click.htm
    http://www.ozgrid.com/forum/showthread.php?t=39696
    http://stackoverflow.com/quest…oto-functions-ctrl-and-f5

  • Search sheets to find match, then update from active sheet, USD $20

    Re: Search sheets to find match, then update from active sheet, USD $20


    Ok, so this code will be fired from a sheet used for a rubric/scoring guide for one assignment (many assignments won't have these rubric sheets).
    So, what is needed is when I update the rubric sheet, which should have the same header info in col C as the related assignment column in some other sheet (actually, only sheets that are named with the prefix "Period "), then col C of the rubric sheet should be copied and pasted back on top of the assignment col in whatever sheet it came from.


    Values of every odd cell below row 12 though should be copied and pasted, as the rubric sheets will contain SUM formulas.


    I as a teacher may also change the total points in the rubric sheet, which is why cell C10 in the header area needs to be copied.


    Bye the way, a rubric is created from an assignment from the following code in Period 2 sheet, but it actually needs to fire when a cell in row 6 is double-clicked--not when any cell is double-clicked, is why I commented it out temporarily.


    'Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


    ' Dim lCol As Long
    ' lCol = Range("IV6").End(xlToLeft).Column 'create a column-based range.


    ' Dim ws As Worksheet 'create a new worksheet to insert.
    ' Set ws = Sheets.Add


    ' Dim WSlabel As Variant 'create a label for use as part of the new worksheet's name.
    ' WSlabel = Target.Offset(3, 0).Value



    ' If Intersect(Target, Range(Cells(6, "AW"), Cells(6, lCol))) Is Nothing Then Exit Sub
    ' Range("B:C").Copy ws.Range("A1")


    ' Columns(Target.Column).Copy ws.Range("C1") 'insert data from the active sheet to the new sheet.


    ' ws.Name = "Rubric " & WSlabel 'name the new WS as "Rubric " and whatever is in cell 9.