Need a cell value to change automatically based on a future date Closed

Question

Hello,
I need to submit weekly reports on the number of students in classes (currently enrolled, graduated, etc.) The problem I have is that sometimes a class doesn't graduate until the following week, so all the other class #s are reported for a current week report, but and only the Graduation #s for that specific class needs to be reported for the following week's report. Those graduation #s need to be removed, or become a `0' value for the report following the following week's report. Does that make sense? I could change the numbers manually but don't want to make the mistake of forgetting to do or overlooking a class like that again. :-/

The spreadsheet I came up does those things I want except will not automatically change the value of M35 to "0" when the Current Weekending Date (F2) is eight days more than (E35) the week ending date of that particular class. 

I was told by a couple colleagues my formula wasn't enough and need to use a VBA/macro. Can someone help me out? Thanks so much in advanced!

I have the following cell values:
F2 = The Current Week Ending date (a formula updates it automatically)
I2 = Weekly Graduation tally of graduation values =SUM('Data Sheet'!M8:M56)
E35 = The individual class week ending date
I35 = # Graduated from the class; must be kept for record keeping 
K35 = Checkbox to keep track of classes submitted in Report; conditionally formatted to refer to L35
L35 = Checkbox value (FALSE when K35 not checked, TRUE when K35 checked)
M35 = Value of I35 depending on  =IF(L35=TRUE,"0", IF(F2+8<E35,"0",I35))

TrowaD · Answer

Hi 76DataSheet,

Could you upload (a sample of) your file to a filesharing site like www.speedyshare.com or ge.tt etc. for better understanding?

Best regards,
Trowa

76DataSheet · Answer

Sorry for the delay, but I was out of town. Here's the link:
http://speedy.sh/26aaY/Sample.xlsx

Thanks!

TrowaD · Answer

Hi 76DataSheet,

Looked at your file and it is not very clear what you are trying to achieve.

If L35 is true then M35 = 0
If L35 is false, but F2+8<E35 is true then M35 = 0

This is what you want right?

Best regards,
Trowa

76DataSheet · Answer

Thanks for your response Trowa! Sorry about the syntax, but I think this is what I am trying to do:

If F35 is Class A, and L35 is true then M35 = 0 

If F35 is Class B, and L35 is true, M35 = 0 but only if F2>E35+8. Otherwise, M35=I35 

If its Class A then nothing fancy to be done, M35 = 0 when its marked Reported.
However, for Class B the Graduated numbers for that class are left blank for the report in which the class begins, because they don't graduate from that class during that week. But I still need to note that class has been reported for the other information, and do so by clicking on the check box that changes Newly Entered, Currently Enrolled, and Graduated numbers to 0 for all classes regardless of the class type. For Class B, the students graduate the week following the initial report, and the number of students graduated will be entered on the 2nd report while the newly entered and currently enrolled will remain 0, as they were entered on the 1st report. When it comes time to submit a 3rd report, a week after the 2nd report, the number of students graduated from Class B during Week 2 should not be included on the report for week 3. At that time I would like M35 = 0 for Class B. 

Thanks for trying,
76DataSheet

TrowaD · Answer

Hi 76DataSheet,

Then the following formula should do the trick:
=IF(AND(F35="Class A",L35=TRUE),0,IF(AND(F35="Class B",L35=TRUE,$F$2>E35+8),0,I35))

Best regards,
Trowa

Need a cell value to change automatically based on a future date

5 responses

Similar discussions

Subscribe To Our Newsletter!