Help needed with Excel Attendance Sheet


  1. Posts : 65
    WINDOWS 10
       #1

    Help needed with Excel Attendance Sheet


    So I have this attendance sheet, with this formula for the days of the month, based on this “CONFIG” sheet, but id like to make the MONTH cell a drop down list, that would change the formula in F16 based on the selected month in the list.
    Help needed with Excel Attendance Sheet-untitled.png
    ALSO
    And this seems a bit greedy but id also like it if when I selected a month like February that has 28 days, or any month that has less than 31 days, the “31st day column” to be automatically hidden, and if after that I selected a month with 31 days, It should be un-hidden, which from my research the only way to do that would be to use a MACRO command which I have little to no understanding about.

    Any help is greatly appreciated, and id settle for just the month drop down list!
      My Computer

  2. Try3's Avatar
    Posts : 7,427
    Windows 10 Home x64 Version 20H2 Build 19042.868
       #2

    ReallyFrustratd said:
    id like to make the MONTH cell a drop down list
    You use the Data tab, Data validation facility to do this.

    If you want a coherent explanation then look in Excel Help. Here's the relevant extract from Excel 2007 Help. Excel 2007 Help - Data Validation.pdf

    My example:
    - I have written them in what seems to me to be the order of easiest explanation.
    - You'd actually set them up in the reverse order - step 3, step 2, step 1.

    1 The result - my dropdown list. I select the cell then click on the dropdown arrow that appears next to it.
    Help needed with Excel Attendance Sheet-show-dropdown-list-.png

    2 To set this up, select the whole column [or individual cells] where you want the dropdown to appear then click on the Data tab then the Data validation control. You need to
    - select In-cell dropdown
    - set the Allow entry to List
    - in Source, enter the worksheet name & cell range where you have written the allowable entries - you will generally want to use absolute cell references such as those shown [$ in a cell address means absolute and will not vary as you copy the formula all the way down your column - look this up if you are not familiar with it because it needs a bit of getting used to].
    Help needed with Excel Attendance Sheet-set-up-data-validation.png

    3 Write out the list of allowable entries that will appear in your dropdown list.
    Help needed with Excel Attendance Sheet-ref-list.png

    Job done.

    Denis
      My Computer

  3. Try3's Avatar
    Posts : 7,427
    Windows 10 Home x64 Version 20H2 Build 19042.868
       #3

    As for hiding cells depending on the month selected I think that might well be handled as a Conditional formatting matter - a topic about which you are an expert
    - Blanking out the cell content [so you do not see it] rather than hiding the cells can be easier to set up and easier to deal with.
    - I suspect that the easiest way to do it would be to put a second column next to your existing list of months [in your Config worksheet] containing the number of days in each month.
    - You can then refer to the list using a XLookup function, assuming Office 365, [which used to be VLookup & HLookup in earlier versions] in your row 16 cells and base a Conditional format on the results.
    - It's possible that you might find it easiest to add a row somewhere with the nitty gritty of the calculation so that row 16 can just apply formats that refer to the calculation's results.
    - You might find something in your Date-Time functions to help you decide how to adjust February for Leap years.
    - If not, see Method to determine whether a year is a leap year - MSDocs.
    - The reason I think Excel 365 might have a function to help with this is that the MSDocs page 'Applies to' list stops at Excel 2016 so there is something different about Excel 2019 & Excel 365. But I could not find anything useful in Excel functions (alphabetical) - OfficeSupport]

    Denis
      My Computer


  4. Posts : 65
    WINDOWS 10
    Thread Starter
       #4

    Try3 said:
    As for hiding cells depending on the month selected I think that might well be handled as a Conditional formatting matter - a topic about which you are an expert
    - Blanking out the cell content [so you do not see it] rather than hiding the cells can be easier to set up and easier to deal with.
    - I suspect that the easiest way to do it would be to put a second column next to your existing list of months [in your Config worksheet] containing the number of days in each month.
    - You can then refer to the list using a XLookup function, assuming Office 365, [which used to be VLookup & HLookup in earlier versions] in your row 16 cells and base a Conditional format on the results.
    - It's possible that you might find it easiest to add a row somewhere with the nitty gritty of the calculation so that row 16 can just apply formats that refer to the calculation's results.
    - You might find something in your Date-Time functions to help you decide how to adjust February for Leap years.
    - If not, see Method to determine whether a year is a leap year - MSDocs.
    - The reason I think Excel 365 might have a function to help with this is that the MSDocs page 'Applies to' list stops at Excel 2016 so there is something different about Excel 2019 & Excel 365. But I could not find anything useful in Excel functions (alphabetical) - OfficeSupport]

    Denis
    oh Denis you are brilliant! I didn't even think of that! many many thanks once again!
      My Computer


 

  Related Discussions
Our Sites
Site Links
About Us
Windows 10 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 10" and related materials are trademarks of Microsoft Corp.

© Designer Media Ltd
All times are GMT -5. The time now is 15:55.
Find Us




Windows 10 Forums