+ Reply to Thread
Results 1 to 53 of 53

Summary sheet from monthly calendars

  1. #1
    Forum Contributor
    Join Date
    02-26-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    248

    Summary sheet from monthly calendars

    Somebody with wings gave me the formula found in my Summary sheet in the WB I will attach. There is no way I can take this to the next level with any kind of similar formula.

    I'd like a summary sheet for each employee via a slicer (?) - something like found on the Summary sheet. But to include specifics - employee name, month, date, leave type...

    Because of the formatting and setup of each months sheet I'm not sure it's even possible.

    Apologies in advance if this is asking for too much.

    Brenda
    Attached Files Attached Files

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Summary sheet from monthly calendars

    Nice file, but first one important thing I want to point out to you.
    Make sure the calendar sheets are all the same as goes for layout.
    The data in rows 1 through 4 columns E through AG should start on row 2 or row 1 not both and different for each calendar sheet
    I don't know where the employee information is stored, I imagine another file since I get a link error to a non existing file on my system.
    Once you have a name you could use VLOOKUP and select the data you want to display for that employee or employees.
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Forum Contributor
    Join Date
    02-26-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    248

    Re: Summary sheet from monthly calendars

    Hi, Keebellah
    I saw your response in my email this morning before I left for work around 6 am but for a little more than an hour before I left I couldn't log on here - it kept timing out. Not sure if it was the site or my ISP but it was frustrating not being able to reply. Anyways, 9 pm ish here but it's Friday so all is good.
    First - there is no data file. The codes are entered daily on the CALENDAR sheet which via macros is transferred to individual month WS and then into the Summary sheet. It's a file that will be used in the field for supervisors. Official numbers are of course available and reports are innumerable. But they all keep their own "unoffical" records which are easier and faster for them to access and a visual to help them quickly see if it's time to pull the official data. lol. My little project here is just a way to make their task easier. I work in an operations/ manufacturing / production environment. Most supervisors have minimal computer experience of any kind unfortunately.

    So, I digress but just a little background to explain why what I provided is what they do indeed call "data." ")

    Long story - all day at work I kept thinking about your suggestion of VLOOKUP and discounted it everytime, hoping that by the time I got back home here someone here would have suggested something that would work. I was thinking of VLOOKUP being useful only for the first instance. I never thought about increasing the number of instances. It was only a little bit before I left work a few hours ago that it clicked in. You are absolutely right, Keebellah. VLOOKUP! Thank you for suggesting it. Sometimes it's not necessary to have someone do... Sometimes a suggestion / another set of eyes / experience is the only thing necessary. So, I'm off - again. lol

    But - and don't you hate buts - lol - for a good hour or so before I came here I did a few quick test runs and I can't perfect the formula I need. Would you be able to help me wrap an IF statement around the VLOOKUP formula? If the result is an "H" or "R" I would like the cell to remain blank. So far the formula I can come up with generates a True or False in all cells. Again, I need the codes to appear if there are any in the calendar but I don't want to see any "H" or "R".
    My formula is - '=VLOOKUP(Staffing_List!$C$7,Jan!$C$11:$AI$111,2,FALSE)=IF(Staffing_List!$C$7,Jan!$C$11:$AI$111="R","").

    Any chance you are able to help with that formula?

    I'd appreciate it.

    Brenda
    Last edited by BDD2015; 04-13-2018 at 11:27 PM.

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Summary sheet from monthly calendars

    The problem was clearly with the forum's hosting server, I couldn't get in either
    Okay, got it but I do get a link to another file error which is missing: the file it points to is named Stn J RDO.xlsm and it's in the folder G:\BD\Work\Helene Gregoire
    Maybe you renamed this file you attached when it was closed but I get this error, I can skip but that is why I got the feeling there was an external reference file.
    I've read what you have written and see if I can help you.
    Still, why does the data in some sheets start on row 1 and i others on row 2?
    Manual Copy and paste errors? Inconsistencies, they break things up

  5. #5
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Summary sheet from monthly calendars

    One other question, are you entering your holidays and weekends manually or do you have a calendar in there checking these days for the selected year on-the-fly?
    Since you file is already macro-embedded, why not write a macro to process the summary and calendars?
    Might even make it faster when you select another month / year

  6. #6
    Forum Contributor
    Join Date
    02-26-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    248

    Re: Summary sheet from monthly calendars

    Hi, again Keebellah

    I think the 4 calendar WS all match, don't they?
    Title Block A1:D4
    Header Row A10

    Not so sure about the Staffing List or Summary sheets. Still a work in progress there. But they aren't the source data so they can have unique structure, can't they? That what you mean?

    For this quick reply - because it's most important - think I figured it out! Not 100% sure because it's after 4 am and I haven't been to bed yet but - but -
    Pretty sure the solution is simply another VLOOKUP, if I even need it.

    Things are such a process for me. Sigh... lol

    But really? Didn't I make something simple overly complicated? Why am I looking for a formula tweak when in the new summary sheet I will create, showing monthly/daily details (similar in structure to the existing Summary sheet that shows monthly totals) I can just exclude the two codes I don't want? Why am I trying to figure out a way to take them out when it seems to make more sense not to even put them in if it's so simple as to not include them?

    Make sense, Keebellah?

  7. #7
    Forum Contributor
    Join Date
    02-26-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    248

    Re: Summary sheet from monthly calendars

    Also, Keebellah. File error? Original filename and location. Still linked? Crap!
    Macros? Already have a few, think they do as you suggest. Maybe even more.

    I am such a contradiction. lol.. Either too difficult or oversimplify... Think I tend to not always include the complete project here when I ask for help. Try to keep it short and simple I think I think. lol Think I think the way my brain works and my "caveman" methods are scary / funny too so again - simple and short. lol

    Really interested in what you think about my thoughts about the VLOOKUP / code exclusion problem but I am off to bed, finally.

    In the meantime. Challenge? Calendar is generated by the dropdowns. RDO is in essence, "weekend." Not all employees get two consecutive days off a week. I did not write the formula in the calendar! Way beyond my abilities. But back to the challenge, Keebellah? Can you figure out what the formula does? lol. Or am I so tired I'm just being silly right now. Night.

  8. #8
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Summary sheet from monthly calendars

    Jan and February yes byt nor march and Calendar, it's the block G1 - AC

  9. #9
    Forum Contributor
    Join Date
    02-26-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    248

    Re: Summary sheet from monthly calendars

    Ya, see that now. Fixed. Thnx

  10. #10
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Summary sheet from monthly calendars

    Hi, now that I have looked at the file and found the reason for the 'missing link' corrected it, it's not another file its the same file but because you renamed it and did not choose save As for the copy on this forum it keeps looking at the folder you have it in (which is not on other peoples disks) and since I do have a G: disk mapped I just created the said directories and placed your file in it renaming it as you had it and the issue is gone.
    Now to go back to your question, the SUMMARY works perfect for your file.

    You say you want a Summary for each employee using a slicer, but you can display one employee now using the slicer on the SUMMARY worksheet.
    If you want to create a worksheet for each employee (101 more), no problem with Excel nowadays, what is the goal?
    Send them the worksheet so they can see their own absence / leave roster?
    Is it for them to fill it out themselves and return it to you or just to inform them?

    If it's just to give them a copy then I think the best is to create a worksheet on-the-fly for the selected employee(s) and publish this sheet as a pdf file and mail it or place it in their personal folder.
    Could you attach a sample worksheet of how you want the layout per employee?

    About your file again, since you copy the MASTER CALENDAR to create the new month calendar for a specific year, why the dropdown for each month and each year in each of these sheets? If its April 2018 then it should stay April 2018, I find that a little strange

    The (Canadian) Holidays I see you have them in separate worksheet, do you have formula code to create a new holiday list for the next year or how do you do it?
    And populating each month calendar is that manual or macro?

  11. #11
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Summary sheet from monthly calendars

    As you can see there are quite a lot of Names and formulas which point to different folders and even missing references, this happens when copying and even worse when referring to packages which may not even be accessible to others
    A sample of the top rows, the list is long.
    Attached Images Attached Images

  12. #12
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Summary sheet from monthly calendars

    Sorry for all this but another point and very important, your macros do not specify a worksheet they apply to to so if a macro is run without knowing what to do it will do it using the at that moment active worksheet with possible loss of data.
    To say it nicely, it's (very) sloppy
    I know it's my pitfall but this type of programming hurts

  13. #13
    Forum Contributor
    Join Date
    02-26-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    248

    Re: Summary sheet from monthly calendars

    Hi, Keebellah

    Sorry for the delay in responding. Hopefully this will be the last Canadian cold that takes me out this year.
    Thanks for mentionning the references and macros... Those are always the kinds of things I clean up last, after I tackle the "mountains". Then when I get to the top I do all that kinda cleanup. Maybe a bad system but I do get to it. lol

  14. #14
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Summary sheet from monthly calendars

    Happy coding, just holler if you need anything

  15. #15
    Forum Contributor
    Join Date
    02-26-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    248

    Re: Summary sheet from monthly calendars

    hahahaha
    hollering...
    need...
    sample will be ready for posting in a few min if ur up for it

  16. #16
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Summary sheet from monthly calendars

    I'm up to it, but don't hold your breath

  17. #17
    Forum Contributor
    Join Date
    02-26-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    248

    Re: Summary sheet from monthly calendars

    Canadian cold, remember? Not much breath in these lungs to hold. lol
    But ok - think what I've got now is the finished product except for the one problem I need help with. Please and thank you - or someone else.
    First - I have added employees with ID's 1-20 beginning in April so there will be no data for them in Jan-Mar.

    Second - I haven't looked at the macros yet so none of them are pointing to specific sheets yet / still.

    Third - and most important -
    If you were to manually change the CALENDAR to a specific month sheet name, the data in the Summary Sheet populates automatically but it doesn't do so in the Yr_Summary Sheet. I need it to do so.

    Any ideas of how I'd get it to populate automatically?

    PS - I don't see how to SAVE AS here. I have renamed the file.
    Attached Files Attached Files

  18. #18
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Summary sheet from monthly calendars

    I've downloaded the file and will take a look this evening after dinner, its 17:30 here (Holland)

  19. #19
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Summary sheet from monthly calendars

    Maybe a silly question but ...
    PS - I don't see how to SAVE AS here. I have renamed the file.
    What do you mean, you don't know how? What's your Excel level?

  20. #20
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Summary sheet from monthly calendars

    This is the result

    It points to all the other missing worksheets.

    It will not show up on you system because the folder it points to exists, but one can't work with this.
    I'll try and help out with the questions but no guarantee it works
    Attached Images Attached Images

  21. #21
    Forum Contributor
    Join Date
    02-26-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    248

    Re: Summary sheet from monthly calendars

    lol Meant "Save As" in this site. Thought that's what you meant. Guess I misunderstood your earlier reply.

  22. #22
    Forum Contributor
    Join Date
    02-26-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    248

    Re: Summary sheet from monthly calendars

    Ya, I know that's the problem. But my problem is that I need to generate new month sheets each month from the CALENDAR sheet. That way the current month list just carries over, they don't have to copy it over. You should ask "them" that question. hehehehe

    But how come it auto pops on the one WS but not the other?

  23. #23
    Forum Contributor
    Join Date
    02-26-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    248

    Re: Summary sheet from monthly calendars

    Maybe the easiest way would be to generate the calendars and then do a macro that will copy current month list over to the new month?

  24. #24
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Summary sheet from monthly calendars

    Third - and most important -
    If you were to manually change the CALENDAR to a specific month sheet name, the data in the Summary Sheet populates automatically but it doesn't do so in the Yr_Summary Sheet. I need it to do so.
    I renamed CALENDAR to MAY and entered STD in one of the names and it worked for one, but lags for others even after pressing F9 or forcing to recalculate the sheet, strange.

  25. #25
    Forum Contributor
    Join Date
    02-26-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    248

    Re: Summary sheet from monthly calendars

    You have to literally go into each cell and press Enter

  26. #26
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Summary sheet from monthly calendars

    I would do it all with macro's

  27. #27
    Forum Contributor
    Join Date
    02-26-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    248

    Re: Summary sheet from monthly calendars

    To copy the data over? Yup,thinking the same thing. Tired of this beast. lol

  28. #28
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Summary sheet from monthly calendars

    I can't seem to find a reason for why it does not 'pop' on the MAY sheet,
    I'll see if I can figure out why the formula is not triggering itsself.

    Looking at this file a lot of questions keep popping up:
    You've got a staffing list, you've taken into account the option to add 20 more if needed.
    Now I can imagine that person A started hist contact 10 yeas ago and it will end in let's say 2020
    Okay, but you got a new person coming in and he/she starts in February 2018 and has been hired for 6 months, so the contact ends end of august.
    This person should not appear in JAN, SEP, OCT NOV and DEC, how do you take account for that? I see no date in service or date out of service
    The H and Weekend columns which call RDO 1 and RDO 2 the formulas are in all the worksheets even if there is no Holiday and a weekend day shifts so what happens there, all manual work?
    The same with the Holidays, you have listen the holidays for 2018, so in 2019 a new list, if these are fixed dates, no problem, but sometimes there are holidays like last Thursday in November, Thanks Giving, or other days like that, this implies more handy work.

    One you have a worksheet for each month, you do not have any need for the drop-down Year and Month in each sheet. Special reason for this?

    The entire thing could be made dynamic by just running a macro that generates the list of Holidays for the selected year, generates the worksheets and populates the staff list.
    maybe so minor extra macros to add new employees and if you add date of birth you can can even foresee retirement

    You can even add a right-click popup menu so that the different codes can be selected (or still entered as you do now) but that would mean more macro support and somebody on your side (you (?) ) to be able to maintain it.

    Sorry about all the comments and ideas the file itself looks great.

  29. #29
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Summary sheet from monthly calendars

    The Months worksheet, was / is this intended as menu sheet to directly select a particular month ?

  30. #30
    Forum Contributor
    Join Date
    02-26-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    248

    Re: Summary sheet from monthly calendars

    Let's see if I can briefly explain -
    Employees are unionized. They bid on their positions. If a position becomes vacant (someone in it has gone on another open position, or someone is off long term) then the position is open and anyone can bid on it.
    That's why an employee may move month to month into another position (row) on the list.
    The "extra" employees are temps. They are brought in for shortfalls from sick, annual, etc. They move around.
    The reason I don't want names removed is because I don't want them to screw up the formulas. Everything is going to be locked except the specific days in the month (so they can code it). Summary sheets especially will be locked. lol
    And the primary calendar sheet is intended as a way to directly change the month.
    There are about 20 secions and they all have different RDO's which is why there are fropdowns for RDO's.
    Did you notice that when you change the dropdown days the R's change in the calendar? And if an R day falls on a stat holiday, the RDO moves forward. Eg - if they have Sat and Sun off and a holidays falls on Sun, they get Monday off and it shows on the calendar as R+. Regular R days are "R" but when it's offset it's "R+".
    You confused yet? lol

  31. #31
    Forum Contributor
    Join Date
    02-26-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    248

    Re: Summary sheet from monthly calendars

    And as for "maintaining" it. Hopefully not!!!!!!
    That's why I am trying to "dummy proof" it.
    I'll jump back in at year end to change calendars or maybe I'll add a few more years to the hidden RDO + Holidays WS.

  32. #32
    Forum Contributor
    Join Date
    02-26-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    248

    Re: Summary sheet from monthly calendars

    And the only thing I really can't overcome is the weekdays in either summary calendar. Didn't put a lot of thought into that but it seemed too much to take on right now. WHich is why I left the R's and H's in the summaries. It tells them what the day of the week is based on their R days.

  33. #33
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Summary sheet from monthly calendars

    Got it, so RDO's are set per month.
    But then again, this file is for one particular year.
    You start a new file every year or you clear this one and start all over?

  34. #34
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Summary sheet from monthly calendars

    It's bed-time here, I'm up early tomorrow (05:50) .
    I'll pick it up tomorrow, the picture your 'painting' is getting a bit clearer every post.
    About the summary per employee, what does that worksheet have to look like?
    I think I asked it some posts back and if a pdf export would help

  35. #35
    Forum Contributor
    Join Date
    02-26-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    248

    Re: Summary sheet from monthly calendars

    We are all inundated with reports coming out our ying yang. Anything we need is already generated somewhere we can access - like retirement days, etc.
    I just try to take existing information and give the supervisors (most who have little computer experience or training) the tools to help them do what they need to get 'er done.
    Last edited by BDD2015; 04-18-2018 at 07:52 PM.

  36. #36
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Summary sheet from monthly calendars

    Understood, I'll just check and see why it doesn't do what it should
    CU tomorrow

  37. #37
    Forum Contributor
    Join Date
    02-26-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    248

    Re: Summary sheet from monthly calendars

    Thank you so much for your help!
    Last edited by BDD2015; 04-18-2018 at 07:51 PM.

  38. #38
    Forum Contributor
    Join Date
    02-26-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    248

    Re: Summary sheet from monthly calendars

    And I think the summary sheet just as it is when you use the slicer to populate the name will work just nicely. I may put borders or make it pretty or something but in essence just what it shows is going to be enough.
    And PDF? hahahahaha No, no, no! That'll just kill or confuse them all. hahaha

  39. #39
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Summary sheet from monthly calendars

    Just to let you know that I haven't forgotten you but I've had less time than I thought I would have.
    I still think a PDF so you can mail it to them and inform them, they don't need to edit, just be up-to-date
    I'll include the option once it's completed, just in case

  40. #40
    Forum Contributor
    Join Date
    02-26-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    248

    Re: Summary sheet from monthly calendars

    NP
    Thank u

  41. #41
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Summary sheet from monthly calendars

    I've been 'cleaning-up' the conditional format rules for each sheet, have you taken a look, they are a mess.
    The reason is that by copying form one sheet to another pr from section to section you also copy the conditional format rules.
    The result is almost as many rules as there are rows in the worksheet.
    I managed to clear them all to the basic rules (the coloured blocks on top) and the H, R R+ and new month.
    It speeds up the calculation (a little) but it makes it better to manage.
    A question:
    After you last explanation of names moving up or down a position, does this mean that when a new month begins, you make a copy of the MASTER_CALENDAR and rename it to the new month (abbreviated), then copy the names form the previous month to this new month?
    Later you move them around if they have changed from position? Is this correct?
    I want to know what to take into account when writing the macros, you have some in your vba code but they are missing in the file you posted so I don't see what these macros did or do to compare or use as starting point.

  42. #42
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Summary sheet from monthly calendars

    Well, I've not heard anything from you (yet) but here's what I've done so far.
    When the file opens it will check the CALENDAR sheet and if it's for the current month it does nothing and takes you to the Months sheet so the user can click on the month button he/sheet wants to see.
    If the CALENDAR worksheet is for the previous month, if its not it renames it to the corresponding month and creates a new CALENDAR worksheet for the current month.
    So when you open the file now it will create the APR worksheet and name it CALENDAR and fill it with the list of names from MAR
    Then it will take you to Months Sheet.
    When you press the image you have on the top right it will always take yo the Months sheet except when in the months sheet it it will check the CALENDAR sheet to see if you're still in that Month.
    Further more every Calendar sheet will have a code that when you right click in the calendar section you can choose the action to perform, you can select as many cells as you want, right click and select Annual WO Su etc.
    Attached Files Attached Files

  43. #43
    Forum Contributor
    Join Date
    02-26-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    248

    Re: Summary sheet from monthly calendars

    Morning, Keebellah
    Apologies. Ottawa has had sunshine for the last two days. Sunshine, Keebellah! What an anomoly. lol. Anyways, been playing... Sorry. But I'm back.
    So, thank you for the WB. As soon as I finish this I am going to check it out.

    Re - above. Absolutely right about the CF rules. Noticed it last week. Like little rabbits pro-creating. lol. Try to avoid CF because I find it hangs up a lot of stuff so never really paid attention to the "multiplication." It's on my radar for the future now though.
    Gonna attach my WB as it is now (new name)- without possible changes as a result of your WB. I ended up doing a calendar for each month cause I couldn't figure out why the yr summary sheet wasn't calculating.
    So - all entries are made in the Calendar sheet. The first macro then copies the WS, renames it "Rename Me" (they have to rename it manually), the second macro copies/paste special/values the WS and the third one opens hidden WS Master_Calendar and copies and pastes the formulas from it back into the CALENDAR then rehides Master_Calendar.
    So here it is and now I'm off to check out yours which I am sure will be much better. Back soon.
    Attached Files Attached Files

  44. #44
    Forum Contributor
    Join Date
    02-26-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    248

    Re: Summary sheet from monthly calendars

    First thing I see is that it doesn't auto populate the codes in the Yr Summary sheet when a month is added.

  45. #45
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Summary sheet from monthly calendars

    True, and that's two reasons, the month APR does not exist because it's still named CALENDAR
    And the rest of the months give REF errors because they do not exist.
    I think the entire thing should be done with macros as far as Summary and yr_Summary goes.
    The file you sent was another department, so that means you have more of those, the number of staff locations is not numbered sequentially so that's an extra variable.
    Not one thing to do in one run.
    Will see what I can do for you, The only thing my last file does is populate the new month and leave it CALENDAR until the next month so Summary and YR_Summary could better be populated by a macro instead of a formula
    Will see what I can come up with.

  46. #46
    Forum Contributor
    Join Date
    02-26-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    248

    Re: Summary sheet from monthly calendars

    Isn't the anomaly the Summary sheet that populates but the Yr_Summary that doesn't? Maybe not so sophisticated or "unsloppy" - lol - but it all kinda works but wouldn't it all be a beaut. if that sheet were to calculate properly? So, maybe the answer is just that freaking sheet or the formula? Gonna look at that now.

  47. #47
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Summary sheet from monthly calendars

    No, the reason the Summary sheet works is because the formulas are all valid.
    In the yr_summary this is what's in the APR row

    Please Login or Register  to view this content.
    It doesn't know what #REF points to, the formula should be like in the Summary using INDIRECT()

  48. #48
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Summary sheet from monthly calendars

    Corrected all the formulas in YR_Summary and it works now for all.
    Check Hicks you missed APR it said JAN FEB MAR JUN MAY JUN etc in yr_summary, is corrected in this file
    No extra macro's just modified code.

    Will place the code in the other file you posted
    Attached Files Attached Files

  49. #49
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Summary sheet from monthly calendars

    Okay, I think I got it all right now.
    Both files have the same macro's and all formulas seem to do their job. Missing is the output for one staff member.
    So please jog my memory, which is the overview you would like to see for one staff member, like yr_summary or like summary?

    The only thing I could not do because the worksheets FEB MAR and APR are password protected is to Assign the macro GoMonths to the image top right (check JAN) in the Tech Services.
    Attached Files Attached Files

  50. #50
    Forum Contributor
    Join Date
    02-26-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    248

    Re: Summary sheet from monthly calendars

    #REF points to WS month name

    don't get INDIRECT so much
    sigh...
    lol

  51. #51
    Forum Contributor
    Join Date
    02-26-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    248

    Re: Summary sheet from monthly calendars

    Hi, Keebellah,

    Are you kidding me?
    I can't believe you stuck with me through this.
    I can't believe you pulled it off!

    I know there's another file(s) from you but I can't get to them yet. I've been here for like two hrs just figuring this out.

    Amazing!

    I am in awe.
    And a little frightened. Lol

    Love how you attributed code you used to the author
    Can't believe you found it
    Can't believe you understood it.

    Because I'm not familiar with INDIRECT can you explain the 'INDIRECT("'"&$B934&"' part of the formula?

    I understand a lot of your WS.
    I LOVE the dropdowns for the codes. Fancy data validation?
    Really gonna have to study this and figure it all out.
    Question - why does WO Pay not have a FaceID # in the ADMIN sheet?
    I was able to add another unique leave code and apply CF to it.
    I also it it as code in the RBtnMouseModule.
    I don't understand the "As Long =" numbers that are assigned in the XlGlobals mod.
    I don't understand what the Const "nada" is but it has the same # as EMP.
    It is the only case of duplicate numbering in the list??

    I don't understand what col. A, D, E, F are on the Admin Sheet

    But on a positive note -
    You PL code in the calendar generated an error.
    I was able to go into the RBtnMouseModule and add the missing code.
    Sorry, Keeballah but giggling here. Lol

    Again, I know you've sent revisions but it's late so the next step wlll have to wait until tomorrow night.
    Besides, I need time to digest this beauty.

    And oh, before I forget. Gonna sound stupid but not sure on the sequence of the macros and which ones of mine are now redundant.
    I think I run mine and yours works in the background. Right?
    But how is the calendar reset?
    Again, late and tired so I'll check that part out again tomorrow too.
    In the meantime, even before we get to your next version, THANK YOU!!

    Can't wait to see what you came up with after this.
    Last edited by BDD2015; 04-23-2018 at 08:41 AM.

  52. #52
    Forum Contributor
    Join Date
    02-26-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    248

    Re: Summary sheet from monthly calendars

    Morning,
    Sorry about the PW - I forgot - but it is 2018
    So, just got up but still in awe.
    Thank you for changing the CF colors.
    Love it all.
    But what's the difference between this version and the previous one?
    You gotta tell me how you put the little images in the dropdowns. Love it!
    All in all, love both versions.
    You are my hero, Keebellah.
    Can't imagine the time you put into this. So very appreciated.
    So, can you take a few min. and answer my questions, incl. the ones from last night.
    Can't promise I won't be back with more "How'd you do that" questions.
    Hope you are having a great day.
    You deserve it!

    BD

  53. #53
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Summary sheet from monthly calendars

    You're welcome.
    both versions are identical as far as the vba code goes.
    The difference in CF colors is your doing, they were like that in each file.
    The images in the dropdowns are part of the right clock popup menu the link to Ron de Bruin's site is wher I got this long time ago and just modified it to my needs in my previous files.
    https://www.rondebruin.nl/win/s6/win002.htm
    you'll also see a link to the buttonfaces which is Excel file showing the ids for the icons.
    The rest is just the formulas correced using the INDiRECT() function (which you also had) but in yr_summary modified to address the month sheet using the name in the row

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help needed using Userform to add events to monthly calendars
    By lax2734 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-06-2013, 04:30 PM
  2. Formula-applying monthly expenses to an end of year summary sheet
    By ksigler in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-31-2013, 01:00 PM
  3. Monthly spreadsheet- need to update summary sheet based on date
    By hootiebsc in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 03-08-2013, 02:17 PM
  4. monthly data summary sheet
    By javed65 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-22-2012, 10:54 AM
  5. monthly data summary sheet
    By javed65 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-22-2012, 07:08 AM
  6. Replies: 2
    Last Post: 06-17-2005, 05:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1