#### B4L4KS

##### Board Regular
how can i create a formula that when a cell equals 1 other cells drop by one
and the same for two but the formula needs to add on continuously

also reset itself once a day

kristian

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### MrKowz

##### Well-known Member
Can you please provide an example of what you want? Are you wanting it to reset the first time you load the spreadsheet each day?

#### B4L4KS

##### Board Regular
hi

i need it so it resets once a day,

preferably at the end of the day

thanks

#### MrKowz

##### Well-known Member
What defines the "end of the day"? Where do you want these cells located in your spreadsheet? I need more details.

#### B4L4KS

##### Board Regular
[RANGE=cls:xl2bb-100][XR][XH=cs:7]Excel Workbook[/XH][/XR][XR][XH][/XH][XH]E[/XH][XH]F[/XH][XH]G[/XH][XH]H[/XH][XH]I[/XH][XH]J[/XH][/XR][XR][XH]9[/XH][XD=cs:2|h:c]Pizza Dough[/XD][XD][/XD][XD=cs:3|h:c]Quantity X Servings[/XD][/XR][XR][XH]10[/XH][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]11[/XH][XD=cs:2|h:c]Flower[/XD][XD][/XD][XD=h:c|cls:fx][FORMULA==COUNTIF(Recept!L16,'Order Form'!H12:I12)*'Order Form'!K12]1[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==IF(H11,-1,0)*H11]-1[/FORMULA][/XD][XD=h:c]400[/XD][/XR][XR][XH]12[/XH][XD=cs:2|h:c]sugar[/XD][XD][/XD][XD=h:c|cls:fx][FORMULA==H11]1[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==I11]-1[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==J11]400[/FORMULA][/XD][/XR][XR][XH]13[/XH][XD=cs:2|h:c]yeast[/XD][XD][/XD][XD=h:c|cls:fx][FORMULA==H12]1[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==I12]-1[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==J12]400[/FORMULA][/XD][/XR][XR][XH]14[/XH][XD=cs:2|h:c]salt[/XD][XD][/XD][XD=h:c|cls:fx][FORMULA==H13]1[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==I13]-1[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==J13]400[/FORMULA][/XD][/XR][XR][XH]15[/XH][XD=cs:2|h:c]water[/XD][XD][/XD][XD=h:c|cls:fx][FORMULA==H14]1[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==I14]-1[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==J14]400[/FORMULA][/XD][/XR][XR][XH]16[/XH][XD=cs:2|h:c]olive oil[/XD][XD][/XD][XD=h:c|cls:fx][FORMULA==H15]1[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==I15]-1[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==J15]400[/FORMULA][/XD][/XR][XR][XH]17[/XH][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]18[/XH][XD=cs:2|h:c]Cheese[/XD][XD][/XD][XD=h:c|cls:fx][FORMULA==H16]1[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==I16]-1[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==J16]400[/FORMULA][/XD][/XR][XR][XH]19[/XH][XD=cs:2|h:c]Tomato puree[/XD][XD][/XD][XD=h:c|cls:fx][FORMULA==H18]1[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==I18]-1[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==J18]400[/FORMULA][/XD][/XR][XR][XH]20[/XH][XD=cs:2|h:c]Tomatos[/XD][XD][/XD][XD=h:c|cls:fx][FORMULA==H19]1[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==I18]-1[/FORMULA][/XD][XD=h:c|cls:fx][FORMULA==J19]400[/FORMULA][/XD][/XR][XR][XH=cs:7][RANGE][XR][XD]Stock Control[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

start of the day 11am
end of the day 12pm midnight

thanks

kristian

#### MrKowz

##### Well-known Member
In the formula:

=COUNTIF(Recept!L16,'Order Form'!H12:I12)*'Order Form'!K12

What cell are you changing to update the value of H11? I'm thinking that we need to have a Worksheet_Change event monitor for the change of that cell you manually adjust so that when you do change it, it will adjust the values in column I.

Also, I might reccomend that the worksheet be "reset" when it is first opened in the morning (unless the worksheet will be open at midnight every night).​

Replies
1
Views
34
Replies
5
Views
68
Replies
7
Views
51
Replies
6
Views
42
Replies
1
Views
27