Feeling bird-brained - Ranges to change in Formula to empty cells but keeping ranges the same prior to changes.

[JohnTester]

Hey Excel Gurus.

I have been spending the past few days playing around with different formulas without success. Even know-it-all Google and "we have a video for that" Youtube cannot help.

Below is the XL2BB of what the spreadsheet looks like on Excel 365.

In Cell C it uses a Sum to see if the total value of the range is 4 or more, then the countdown from 24 to 0 days starts.
Once the eggs hatch(Cell D) the formula should zero out and the range for the Sum must start from the cell in C below the D cell containing the number of eggs hatched.

I have Tried INDEX, MATCH, SUMIF, AND, OR, DSUM and and and without success.
Please, please help!

I don't care if it's formula or VBA at this point.

2. February Breeding.xlsx
	A	B	C	D	E	F
2	DATE		Amount of Eggs	Total Babies	Days to go	Days passed
3
4
5	Mon-31-Jan-22		0	0	0	0
6	Tue-01-Feb-22				0	0
7	Wed-02-Feb-22		4		24	1
8	Thu-03-Feb-22				23	2
9	Fri-04-Feb-22				22	3
10	Sat-05-Feb-22				21	4
11	Sun-06-Feb-22				20	5
12	Mon-07-Feb-22				19	6
13	Tue-08-Feb-22				18	7
14	Wed-09-Feb-22				17	8
15	Thu-10-Feb-22				16	9
16	Fri-11-Feb-22				15	10
17	Sat-12-Feb-22				14	11
18	Sun-13-Feb-22				13	12
19	Mon-14-Feb-22				12	13
20	Tue-15-Feb-22				11	14
21	Wed-16-Feb-22				10	15
22	Thu-17-Feb-22				9	16
23	Fri-18-Feb-22			2	0	0
24	Sat-19-Feb-22				0	0
25	Sun-20-Feb-22				0	0
26	Mon-21-Feb-22				0	0
27	Tue-22-Feb-22				0	0
28	Wed-23-Feb-22				0	0
29	Thu-24-Feb-22				0	0
30	Fri-25-Feb-22				0	0
31	Sat-26-Feb-22				0	0
32	Sun-27-Feb-22				0	0
33	Mon-28-Feb-22				0	0
34	Tue-01-Mar-22		4		24	1
35	Wed-02-Mar-22				23	2
36	Thu-03-Mar-22				22	3
37			8	2
Hok1

Cell Formulas
Range	Formula
C5	C5	=--IF('C:\Users\HP Elite\Desktop\Work\Leonard\Birds\[1. January Breeding.xlsx]Hok1'!$F$36=0,0,'C:\Users\HP Elite\Desktop\Work\Leonard\Birds\[1. January Breeding.xlsx]Hok1'!$C$37)
D5	D5	='C:\Users\HP Elite\Desktop\Work\Leonard\Birds\[1. January Breeding.xlsx]Hok1'!$D$37
E5	E5	=IF(E6-1<=0,0,E6+1)
F5	F5	=--IF('C:\Users\HP Elite\Desktop\Work\Leonard\Birds\[1. January Breeding.xlsx]Hok1'!$F$36<=0,0,'C:\Users\HP Elite\Desktop\Work\Leonard\Birds\[1. January Breeding.xlsx]Hok1'!$F$37)
E6	E6	=IF(AND($C$5>=4,$F$5=24),IF(SUM($D$5:D6)=0,IF(SUM($C$6:C6)>=4,$H$1-F5,0),0),IF(SUM($D$5:D6)=0,IF(SUM($C$5:C6)>=4,$H$1-F5,0),0))
F6:F33,F35:F36	F6	=IF(SUM($F$5:F5)>299,0,IF(E6>0,IF(SUM($C$5:C6)>=4,1+F5),0))
E7:E33	E7	=IF(SUM($E$5:E6)>=299,0,IF(AND($C$5>=4,$F$5=24),IF(SUM($D$5:D7)=0,IF(SUM($C$6:C7)>=4,$H$1-F6,0),0),IF(SUM($D$5:D7)=0,IF(SUM($C$5:C7)>=4,$H$1-F6,0),0)))
A5	A5	='Over View'!C42-1
A6	A6	='Over View'!C42
A7:A36	A7	=A6+1
C37:D37	C37	=SUM(C6:C36)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A33:G33	Expression	=OR(WEEKDAY($A$5)=1,WEEKDAY($A$5)=7)	text	NO
A34:G34	Expression	=OR(WEEKDAY($A$6)=1,WEEKDAY($A$6)=7)	text	NO
A35:G35	Expression	=OR(WEEKDAY($A$7)=1,WEEKDAY($A$7)=7)	text	NO
A36:G36	Expression	=OR(WEEKDAY($A$8)=1,WEEKDAY($A$8)=7)	text	NO
A34:G34	Expression	=OR(WEEKDAY($A$13)=1,WEEKDAY($A$13)=7)	text	NO
A32:G32	Expression	=OR(WEEKDAY($A$19)=1,WEEKDAY($A$19)=7)	text	NO
A32:G32	Expression	=OR(WEEKDAY($A$18)=1,WEEKDAY($A$18)=7)	text	NO
A31:G31	Expression	=OR(WEEKDAY($A$17)=1,WEEKDAY($A$17)=7)	text	NO
A30:G30	Expression	=OR(WEEKDAY($A$16)=1,WEEKDAY($A$16)=7)	text	NO
A29:G29	Expression	=OR(WEEKDAY($A$15)=1,WEEKDAY($A$15)=7)	text	NO
A28:G28	Expression	=OR(WEEKDAY($A$14)=1,WEEKDAY($A$14)=7)	text	NO
A27:G27	Expression	=OR(WEEKDAY($A$13)=1,WEEKDAY($A$13)=7)	text	NO
A26:G26	Expression	=OR(WEEKDAY($A$19)=1,WEEKDAY($A$19)=7)	text	NO
A25:G25	Expression	=OR(WEEKDAY($A$18)=1,WEEKDAY($A$18)=7)	text	NO
A24:G24	Expression	=OR(WEEKDAY($A$17)=1,WEEKDAY($A$17)=7)	text	NO
A23:G23	Expression	=OR(WEEKDAY($A$16)=1,WEEKDAY($A$16)=7)	text	NO
A22:G22	Expression	=OR(WEEKDAY($A$15)=1,WEEKDAY($A$15)=7)	text	NO
A21:G21	Expression	=OR(WEEKDAY($A$14)=1,WEEKDAY($A$14)=7)	text	NO
A20:G20	Expression	=OR(WEEKDAY($A$20)=1,WEEKDAY($A$20)=7)	text	NO
A19:G19	Expression	=OR(WEEKDAY($A$19)=1,WEEKDAY($A$19)=7)	text	NO
A18:G18	Expression	=OR(WEEKDAY($A$18)=1,WEEKDAY($A$18)=7)	text	NO
A17:G17	Expression	=OR(WEEKDAY($A$17)=1,WEEKDAY($A$17)=7)	text	NO
A16:G16	Expression	=OR(WEEKDAY($A$16)=1,WEEKDAY($A$16)=7)	text	NO
A15:G15	Expression	=OR(WEEKDAY($A$15)=1,WEEKDAY($A$15)=7)	text	NO
A14:G14	Expression	=OR(WEEKDAY($A$14)=1,WEEKDAY($A$14)=7)	text	NO
A13:G13	Expression	=OR(WEEKDAY($A$13)=1,WEEKDAY($A$13)=7)	text	NO
A12:G12	Expression	=OR(WEEKDAY($A$12)=1,WEEKDAY($A$12)=7)	text	NO
A11:G11	Expression	=OR(WEEKDAY($A$11)=1,WEEKDAY($A$11)=7)	text	NO
A10:G10	Expression	=OR(WEEKDAY($A$10)=1,WEEKDAY($A$10)=7)	text	NO
A9:G9	Expression	=OR(WEEKDAY($A$9)=1,WEEKDAY($A$9)=7)	text	NO
A8:G8,E7:F7	Expression	=OR(WEEKDAY($A$9)=1,WEEKDAY($A$9)=7)	text	NO
A7:D7,G7	Expression	=OR(WEEKDAY($A$7)=1,WEEKDAY($A$7)=7)	text	NO
A6:G6	Expression	=OR(WEEKDAY($A$6)=1,WEEKDAY($A$6)=7)	text	NO

 

[Peter_SSs]

This appears to be a duplicate to: Range that auto-adjusts depending on Value in Column C and D

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.