Populate back values entered based on value of a specific cell

[pragnya_adi]

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.

[#2]

@pragnya_adi 

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.

[#3]

Hi Hans,
Thanks for the info.
Since I'm not much aware of it, so could be able to share some code for the VLOOKUP or XLOOKUP formula for this scenario.

And also if you could share links to few of the attendance tracker templates that would be achieving the scenario that I want to implement.

[#4]

@pragnya_adi 

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.

[#5]

@HansVogelaar 

Please find the google drive link for the excel that I have prepared till now.

https://docs.google.com/spreadsheets/d/1a5KFtjJlau_kINytC7Uzg63Aw43gSBvW/edit?usp=drive_link&ouid=11...

[#6]

@pragnya_adi 

When I click your link, I get an "Unauthorized" error.

[#7]

Sorry for it.. Can you please try again.
Given the access now.

[#8]

@pragnya_adi 

Thanks, I'll have look later on.

[#9]

@pragnya_adi 

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.

[#10]

@HansVogelaar
Thanks for it. It's very good.

But is there a way to do it in the reverse way like
We enter the values in Sheet1 as "a" or "p" and the values w.r.t it gets written in the "List" sheet as we are now filling up.

[#11]

@pragnya_adi 

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.

[#12]

@pragnya_adi 

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.

[#13]

@HansVogelaar

Thanks for it.. I understood it now why it can't be done.

[#14]

@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)...

[#15]

@djclements 

Thanks for it.. These are quite helpful.