PDA

View Full Version : Stop calculating a column once the date has passed



Teacher
09-25-2018, 07:51 PM
Hi all,

I am a teacher and I have an excel document that has my seating chart. I have created a checkbox in the seating chart that kids click on when they come in to class (each kid has a checkbox). That spreadsheet is linked then to my attendance spreadsheet. Here is where I'm having the issue.

My current attendance spreadsheet has dates of the quarter across row 3. Student names are in column A. Basically my "attendance grid" runs from B3:AX33. In those cells I have a formula that links to the "true/false" result from the kids seating chart spreadsheet. If it is true, do nothing, If False, mark an A. Currently this formula is an if statement based upon the date in Row 3...

"IF(A3=Today(), ...........)"

The problem I'm having is that today it erased what I had in the spreadsheet yesterday. Is there something I can do to keep the values in there after the date has passed? So basically if A3 is < today's date then don't recalculate that column.

I want it to auto calculate based on values in the seating chart spreadsheet, but only on that date - not ahead of the date and not after the date has passed.

Pichon
09-26-2018, 07:52 PM
I'm not sure if this is what you are looking for, but assuming that your sheets are called SeatingChart and Attendance, in the Attendance sheet, could you put the following formula in cell B4 and fill the rest of the rows and columns with the fill handle for the dates and students:

=IF(SeatingChart!B$3<=Today(),IF(SeatingChart!B4=True,"A",""),"")

Pichon
09-26-2018, 08:01 PM
Okay, upon rereading your question, that is not what you're looking for. This looks like a problem that formulas won't easily solve and VBA is probably the better choice.

Is this (see attached) what you want to do?

When you click Record Attendance at the end of the day, the Falses will be recorded as absences on the day that matches today. They won't disappear once recorded.