Need Serious Help with an Adding Formula

[REDROCK]

Hello!

After much trial and error and non-stop failing, I am reaching out to anyone who may be able to help. My knowledge is just simply not at a level high enough to be able to figure this out.

Basically what I need is this:

I am trying to keep track of an item that is used every day of the year. For each day, I would like to enter a total number (which will be the total number of glasses used for that day) that will keep adding up. Once that number reaches 2500, I want the box to turn Red to help remind us that the number is at 2500. The tricky part that I can't figure out is this: Once that number reaches 2500, I want the "total" to reset to ZERO so the process can begin again.

What we do is use a certain amount of glasses for our people. Once the total is 2500 (which includes the number of uses that the glasses would be getting), we throw away all the glasses and open up new boxes. This process begins again, as we hit that 2500 mark and then throw them away for new glasses. This is to help ensure that we are getting a few uses out of each pair of glasses (and yes, they are cleaned in between uses!!). What I have been trying to figure out is how the heck to make a spreadsheet that will simply allow us to enter in the daily total and reset at 2500 automatically so we can continue to enter in daily numbers.

Make sense? Any help you can give would be GREATLY appreciated!! I have been messing with this for like 3.5 hours and am making no progress.

Thanks for taking the time to read!!!

-MJ

[JapanDave]

Download the workbook, enter 500 in the remaining glasses number in column B, this will give you a total of 2500 glasses.

See if what results is what you are after?
Sheet1 is always the data entry sheet and as you get to 2500 glasses, a new sheet will be made to stored the dates you opened glasses.

[dilipandey]

Hi MJ,

See the attached file which may serve your purpose.
Whenever you reach 2500 glass count, you need to use next available section on right side.

Try entering number greater than 3 at yellow cell, it won't allow you as till 3 total is 2500

Regards,
DILIPandey

<click on below 'star' if this helps>

[REDROCK]

Both have been VERY helpful!!! Thank you so much for your help!!

[REDROCK]

OK actually one final question for you both!

Is it possible for that number to allow you to surpass 2500? For example, it would be nearly impossible for us to predict exactly when we will hit 2500. Occasionally, we may use an extra few in a day, resulting in us recylcing the glasses at 2509 or 2600 even. Is there a way to do exactly what you have done, only allow that number to pass 2500 and still prompt us to exchange? That way we can temporarily pass 2500 and still be able to toss them out.

Thanks!!

[dilipandey]

You are welcome RedRock..!!

Cheers

Regards,
DILIPandey

<click on below 'star' if this helps>

[dilipandey]

Hi RedRock,

See the updated attachment where I have relaxed the criteria as per your wish..


Regards,
DILIPandey

<click on below 'star' if this helps>

[REDROCK]

I can't thank you enough. Very much appreciated, you are a life saver! Thanks

[dilipandey]

Hey RedRock.. you are welcome....

cheers

Regards,
DILIPandey

<click on below 'star' if this helps>

[JapanDave]

Try the new workbook, enter some data that will bring the total over 2500.