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

Status
Not open for further replies.

JohnTester

New Member
Joined
Sep 25, 2018
Messages
9
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.

Cell Formulas
RangeFormula
C5C5=--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)
D5D5='C:\Users\HP Elite\Desktop\Work\Leonard\Birds\[1. January Breeding.xlsx]Hok1'!$D$37
E5E5=IF(E6-1<=0,0,E6+1)
F5F5=--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)
E6E6=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:F36F6=IF(SUM($F$5:F5)>299,0,IF(E6>0,IF(SUM($C$5:C6)>=4,1+F5),0))
E7:E33E7=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)))
A5A5='Over View'!C42-1
A6A6='Over View'!C42
A7:A36A7=A6+1
C37:D37C37=SUM(C6:C36)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A33:G33Expression=OR(WEEKDAY($A$5)=1,WEEKDAY($A$5)=7)textNO
A34:G34Expression=OR(WEEKDAY($A$6)=1,WEEKDAY($A$6)=7)textNO
A35:G35Expression=OR(WEEKDAY($A$7)=1,WEEKDAY($A$7)=7)textNO
A36:G36Expression=OR(WEEKDAY($A$8)=1,WEEKDAY($A$8)=7)textNO
A34:G34Expression=OR(WEEKDAY($A$13)=1,WEEKDAY($A$13)=7)textNO
A32:G32Expression=OR(WEEKDAY($A$19)=1,WEEKDAY($A$19)=7)textNO
A32:G32Expression=OR(WEEKDAY($A$18)=1,WEEKDAY($A$18)=7)textNO
A31:G31Expression=OR(WEEKDAY($A$17)=1,WEEKDAY($A$17)=7)textNO
A30:G30Expression=OR(WEEKDAY($A$16)=1,WEEKDAY($A$16)=7)textNO
A29:G29Expression=OR(WEEKDAY($A$15)=1,WEEKDAY($A$15)=7)textNO
A28:G28Expression=OR(WEEKDAY($A$14)=1,WEEKDAY($A$14)=7)textNO
A27:G27Expression=OR(WEEKDAY($A$13)=1,WEEKDAY($A$13)=7)textNO
A26:G26Expression=OR(WEEKDAY($A$19)=1,WEEKDAY($A$19)=7)textNO
A25:G25Expression=OR(WEEKDAY($A$18)=1,WEEKDAY($A$18)=7)textNO
A24:G24Expression=OR(WEEKDAY($A$17)=1,WEEKDAY($A$17)=7)textNO
A23:G23Expression=OR(WEEKDAY($A$16)=1,WEEKDAY($A$16)=7)textNO
A22:G22Expression=OR(WEEKDAY($A$15)=1,WEEKDAY($A$15)=7)textNO
A21:G21Expression=OR(WEEKDAY($A$14)=1,WEEKDAY($A$14)=7)textNO
A20:G20Expression=OR(WEEKDAY($A$20)=1,WEEKDAY($A$20)=7)textNO
A19:G19Expression=OR(WEEKDAY($A$19)=1,WEEKDAY($A$19)=7)textNO
A18:G18Expression=OR(WEEKDAY($A$18)=1,WEEKDAY($A$18)=7)textNO
A17:G17Expression=OR(WEEKDAY($A$17)=1,WEEKDAY($A$17)=7)textNO
A16:G16Expression=OR(WEEKDAY($A$16)=1,WEEKDAY($A$16)=7)textNO
A15:G15Expression=OR(WEEKDAY($A$15)=1,WEEKDAY($A$15)=7)textNO
A14:G14Expression=OR(WEEKDAY($A$14)=1,WEEKDAY($A$14)=7)textNO
A13:G13Expression=OR(WEEKDAY($A$13)=1,WEEKDAY($A$13)=7)textNO
A12:G12Expression=OR(WEEKDAY($A$12)=1,WEEKDAY($A$12)=7)textNO
A11:G11Expression=OR(WEEKDAY($A$11)=1,WEEKDAY($A$11)=7)textNO
A10:G10Expression=OR(WEEKDAY($A$10)=1,WEEKDAY($A$10)=7)textNO
A9:G9Expression=OR(WEEKDAY($A$9)=1,WEEKDAY($A$9)=7)textNO
A8:G8,E7:F7Expression=OR(WEEKDAY($A$9)=1,WEEKDAY($A$9)=7)textNO
A7:D7,G7Expression=OR(WEEKDAY($A$7)=1,WEEKDAY($A$7)=7)textNO
A6:G6Expression=OR(WEEKDAY($A$6)=1,WEEKDAY($A$6)=7)textNO
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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.
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top