Apr 16 2024 03:15 AM
I'm creating this attendance tracker in excel.
As you can see that there are entries like a, p for various dates for the month of Jan (B2 cell). These entries are done manually for each date.
What I want is that when I change the B2 cell value to Feb, they should become blank or should populate values previously entered for Feb month.
And likewise when I switch back to Jan, it should populate the above values.
NOTE: In B2 cell, I have created a list from Jan to Dec. And I want to do the same for all months.
Apr 16 2024 03:34 AM
You should have a separate sheet that lists the codes for the entire year. You can then use a VLOOKUP or XLOOKUP formula to display the codes on the sheet from your screenshot.
Microsoft has several attendance tracker templates that do this.
Apr 16 2024 03:49 AM
Apr 16 2024 04:05 AM
If you'd like help with your workbook, please attach a copy of it without sensitive information, or make it available on Google Drive, OneDrive, Dropbox or similar.
To find existing templates, select File > New in Excel.
Type attendance tracker or absence tracker in the search box, then press Enter.
Apr 16 2024 10:14 PM
Please find the google drive link for the excel that I have prepared till now.
Apr 17 2024 01:20 AM
When I click your link, I get an "Unauthorized" error.
Apr 17 2024 07:21 AM
Apr 17 2024 09:02 AM
Thanks, I'll have look later on.
Apr 17 2024 12:30 PM
See the attached version. I added a new sheet named List. Enter the codes a and p here.
Sheet1 is just for display purposes - I protected the sheet without a password. You can only change the year and month. The codes are looked up from the List sheet using formulas.
If you want to add or edit names, you can unprotect Sheet1 temporarily.
Apr 17 2024 10:28 PM
Apr 18 2024 02:09 AM
Excel is an example of a functional programming language which is designed to prevent you the changes you want from happening. Excel values do not evolve during a calculation; they are immutable and, once calculated, their state does not change. Your 'form' on sheet 1 is not allowed to change the state recorded on the database sheet 'List'.
To get a workbook to operate the way you require, you will need to move away from basic worksheet formulae and use an imperative programming environment such as VBA or Microsoft Forms that will allow state change. Your Sheet 1 ceases to be a data input mechanism and is simply a report featuring data extracted from the data table.
Apr 18 2024 03:07 AM
To use the same sheet for entering data in any month AND to display the data for any month would be very complicated.
An alternative, but not a very attractive one, would be to create a separate input sheet for each and every month (so a sheet for January 2024, another sheet for February 2024, etc.), and then use the sheet that you already have to display the data for the selected month and year.
Apr 18 2024 08:20 PM
Apr 19 2024 03:40 AM - edited Apr 19 2024 05:10 AM
@pragnya_adi An alternative method would be to use an Excel Table with multiple rows for each person, for each period, and a Slicer to select the period and filter the table. The attached workbooks demonstrate how this could be setup (note: columns A, B and C are grouped/hidden from view... click the [+] symbol in the top margin to expand/unhide them)...
Apr 20 2024 03:28 AM
Thanks for it.. These are quite helpful.