Posts by Trev81

    Alright, I think I figured out how it's going to work. I found this formula that can reference any date and return the Monday for that week, so I put that in the top left cell of the linear calendar:

    Code
    1. =Date-WEEKDAY(Date,2)+1


    Every other cell just references that one, and adds a +1 that increments for each column or a +3 if going from Friday to Monday. I cheated having to type +2, +3, +4, +5, etc. etc. by using the COLUMNS function to count $A$1:A1, and then had the fill handle increment it for me. I also found out that you can hit CTRL + J while you're setting up custom formatting to enter a soft return, so I found a conditional formatting rule that identifies the first working day of any month, and set it up to change the formatting to mmm↵d:

    Code
    1. =A1=WORKDAY(DATE(YEAR(A1),MONTH(A1),0),1)


    Now everything seems to be working like it should!


    And I totally see what you meant about calculating the Student Attendance column, it's a lot trickier than it seems. I ended up calculating all of the non attendance days for each row, then subtracting that from a COUNT formula and it's been doing good so far. But only time will tell!

    Thanks! Google did most of the heavy lifting, but I'll totally take credit for it :wink:


    Ah yes, the calculation from the current calendar. I can upload it, but... how can I explain... Well it's mostly an INDEX function, in that the user takes their index finger and counts how many attendance days are in the row, and then manually types the number in the right column. You know, like a caveman would. I believe this is also why they use shapes instead of fill colors to indicate which dates have events. It's easier to understand when drawing similar shapes on stone walls is a large portion of your ability to communicate.


    The current idea I have is to use the hidden sheet where the reference cells for the drop down list are, and copy the formulas from the Staff sheet's calendar cells to it, but arranged into a single column. I feel like it'd be way easier to reference that than trying to use the existing calendar cells as an array. Does that sound like a good approach?? It kinda feels like cheating.

    GREETINGS OZGRID!


    So we've been manually updating this monstrosity of a linear school calendar at work for several years (may be NSFL): [ATTACH=JSON]{"data-align":"center","data-size":"large","data-attachmentid":1200188}[/ATTACH]
    THIS year we have a new superintendent, and you can imagine how excited I got when he suggested that we redesign the calendar. I went out and found a pre-made annual calendar with a spin button that changes the displayed year and automatically updates the layout of the corresponding dates in the cells, which I haven't been able to entirely figure out. But after several days of intense Google Fu, what I managed to come up with is a spreadsheet that takes manually entered data for the dates of our various events on the first sheet, and automatically generates calendars in the two formats we use: a parent calendar and a staff calendar.

    I'm really proud of how it turned out, but the price for my hubris was the discovery that the linear format of our old calendar served a very important purpose. Each row of the linear calendar corresponds to an attendance month, and the total attendance days of that month are listed in the last column in the row. Apparently that's useful information for the secretaries and attendance clerks, and the layout also made it easier for the administrative staff to plan out the attendance year.



    Unfortunately I actually like the people I work with, so I don't want them to lose the functionality of the old linear calendar with the redesign. My current solution is to include a calendar in that format in the spreadsheet that will be generated from the same manually entered data, and this is where I'm stuck.


    I attached the spreadsheet, and here's a summary of how it works: The pre-made calendar had formulas in the cells that referred to Names that appear to determine the first day of the month by referencing the value of the cell that contains the year, as near as I can tell. There's a Name for each month in the format of AprSun1, AugSun1, DecSun1, etc. etc. that are structured like this (using August as an example):

    Code
    1. =DATEVALUE("8/1/"&'Staff'!$P$5)-WEEKDAY(DATEVALUE("8/1/"&'Staff'!$P$5))+1


    Then the formulas in each cell look like this:

    Code
    1. =IF(AND(YEAR(AugSun1+1)=$P$5,MONTH(AugSun1+17)=8),AugSun1+1, "")


    With cell P5 containing the year, and the +1 after AugSun1 incrementing by one for subsequent cells (and by 3 when wrapping around from Friday to Monday). I managed to split the year by putting =P5+1 in cell V5, and then changing $P$5 to $V$5 in the Name formulas for JanSun1 through JulySun1. I also created a "FirstDay" Name that's an absolute reference to the value in the "First Day of School" cell in the Data sheet (cell K6), and an "AugMonth" Name that's an absolute reference to all of the date cells in August on the Staff sheet (cells A9:E14).


    So in the linear calendar, I'm able to reference "FirstDay" with a VLOOKUP and fill the top "First Week" columns with the dates for the first week of school by using this formula:

    Code
    1. =VLOOKUP(FirstDay,AugMonth,1,TRUE)


    I incremented the column number in the VLOOKUP by 1 for each subsequent cell in the week, and it fills those in just fine. Then in the second week, I copied all of those formulas over but added a +7 after "FirstDay":

    Code
    1. =VLOOKUP(FirstDay+7,AugMonth,1,TRUE)


    I was hoping that incrementing the added amount by 7 would work for every subsequent week since it would just increase the date code by 7 each time, but this fell apart once I got to +21 and the formula starting returning the dates for the last week of August again. I'm pretty certain I need to do something like include the cells for the rest of the months into the "AugMonth" Name, and then use INDEX instead of VLOOKUP so I can include an area number as well? I haven't played with it yet, but that's what I'm thinking.


    The other problem is even if I increment the dates of the subsequent weeks correctly, every cell in each row in the original linear calendar was actually two cells merged vertically, so that the first day of a new month could be unmerged and the name of the month included in the top cell. I have no idea how to have the formula check to see if the date in a cell is the first day of a new month, and to include a mmm format label when it does. So far I've done everything with cell formulas and conditional formatting, since nothing seemed intense enough for a VBA script. But I'm not sure which approach I should take to best achieve this result, or if there might be an easier to read format for the linear calendar or something.


    Do you wizards have any advice or suggestions on how to do this? I'd appreciate any help you can give me!

    Re: Returning multiple values with a UDF requires entry in cell AFTER those matching


    Alright, I seem to have fixed it. Changing the formula in the cells to include columns A through C instead of just A now returns all entries from the List sheet correctly, without having to modify the actual code itself. So the example I gave of:


    =getnames(B16,List!$A$2:$A$85)


    would now be:


    =getnames(B16,List!$A$2:$C$85)


    And everything works out. Though would you recommend changing the code to directly reference the cells in columns B and C instead of using OFFSET? That seems like it'd be better... form? Or practice? Whichever term it is :)

    Hey guys! Sorry in advance for the vague title, couldn't figure out how to summarize this problem succinctly. Here's my issue:


    In my spreadsheet, I have two sheets: Calendar, and List. The Calendar sheet is essentially the "face" sheet. It's formatted to look nice and be easy to read (I've attached an example). The List sheet is just that. A list of dates in column A, school names (as three letter acronyms) in column B, and student names in column C. The cells in the Calendar sheet contain a list of student names followed by the school acronym in parentheses and then a soft return. The names and school acronyms are retrieved from the rows with corresponding dates in the List sheet.


    To do this, I followed the advice given in this thread for returning the results of multiple values to a single cell with a UDF, and modified the code to fit my specific situation. I wound up with this:


    Code
    1. Function getnames(DRng As Date, LURng As Range)
    2. For Each ce In LURng
    3. If ce.Value = DRng Then
    4. holder = holder & ce.Offset(0, 2).Value & " (" & ce.Offset(0, 1).Value & ")" & Chr(13) & Chr(10)
    5. End If
    6. Next ce
    7. getnames = Left(holder, Len(holder) - 2)
    8. End Function


    So the formula in each cell follows this format (I'll use the entry from B17 in the example): =getnames(B16,List!$A$2:$A$85)


    The DRng in the formula references the cell that contains the date it corresponds to (which is always the cell directly above it), so any future iterations of this spreadsheet only need to have those dates updated and the formulas will still return the correct results. This works great, except that any time I enter new information on the List sheet, the last value in the corresponding cell of the Calendar sheet is returned blank. So, for example, if I enter two student names from the GPT school with a date of 11/3/2012 (let's say John Smith and Jim Smith), the result in the 11/3/2012 cell will be :


    John Smith (GPT)
    ()


    I figured out that if I enter ANYTHING in column A of the next row (after the one with Jim Smith), then immediately delete it, the result then returns properly. Unfortunately, modifying this UDF is my first introduction to VBA, so I lack the knowledge to understand why this happens. Can anybody help?