Stop add #'s when cell value >"X"

[anubis]

Hello everyone.

I am having trouble with the following data. The numbers in D4:I13 are constant. The numbers in E3:H3 are variable. When the total # of dogs (J3)<=20, the numbers in E13:H13 are fine. However, when J3 >20, I want to retain the numbers from when the total dogs was 20 and add the corresponding value from column I.

For example, if G3 were changed from 5 to 6, J3 would be 21, J2 would be 1, G13 would remain 2, I13 would be 2, giving a total of 14 in J13.

I appreciate any help anyone can give, but be gentle. The only experience I have in macros is copying and pasting from message boards.

Anubis
office XP professional v.2002

mrexcelhelp.xls
	D	E	F	G	H	I	J
2	#ofdogs	labs	doberman	husky	gold.retr.	over20	0
3		5	5	5	5		20
4	1	1	1	1	1	2
5	2	1	2	1	1	2
6	3	2	2	1	1	3
7	4	3	2	2	2	4
8	5	4	3	2	3	5
9	6	4	3	3	3	6
10	7	5	4	3	4	7
11
12
13		4	3	2	3	0	12
Sheet1

 

[anubis]

Sorry for the bump (if this even bumps I'm very new to the board). I can gladly say though, from surfing the board, I now know how to use VLOOKUP instead of the nested if functions in row 13. Thanks for that help.

If anyone can help with the original problem, I am still interested in learning how to solve this problem and would be grateful for any information.


Dave

 

[parry]

Hi Dave, welcome to the board.

Im sorry you havent had a reply yet. I must say Im a bit confused with what your trying to achieve. It seems you want certain values to remain constant when it reaches a certain level. This can be done with a formula if theres conditions to state when it remains at a particular point. eg If(x>=20,20,x).

I dont know if you need to resort to VBA but its certainly the way to go if static values are required, but a formula solution may be possible. Could you explain this a bit more please.

For example, if G3 were changed from 5 to 6, J3 would be 21, J2 would be 1, G13 would remain 2, I13 would be 2, giving a total of 14 in J13.

So why would J3 be 21, J2 be 1 etc. Please explain the logic behind this.

 

[anubis]

Hi Parry. Thank you for the response. I'm just on my way out the door form work and can't respond just yet. I will reply again with some more information (hopefully logical and detailed) when I arrive home.

Dave

 

[anubis]

You've got me on the logic issue. First, the subject title is a bit of a misnomer. What I am trying to do is to stop pulling data from some columns (E,F,G,H) and start pulling data from another column (I) when a cell value (J3) exceeds 20. Yet, I need to add the number from column I to sumE13:H13 when J3 was at 20.

Second, the "dog" table is not the true application data. The real scenario is, I believe, too detailed to try to explain here. Because of that, I created this as an example. The overall concept is basically the same, so I am going to try to explain my problem using this example. If I still cannot explain it well enough, I can try explaining the "true" application later. I am not trying to deceive anyone by doing this, I just think it will be easier.

With that said:

Consider this to be a spreadsheet for determining how much dog food to have in-house based on the number of dogs (both number of breed and total number of dogs) for a kennel.

The numbers in E3:H3 are the total number of dogs for that breed. These numbers are increased over time, and there are actually 28 columns including the "over 20" column. The numbers for each breed could range from 0 to 30. (therefore there are 30 rows of corresponding numbers)
The total number of dogs in the facility is J3.
The numbers in E4:H10 are cases of dog food. The amount of dog food needed is based on the number of dogs of that breed in the facility. These numbers are constant.
J2 is the number of dogs above 20.
J13 is the total number of cases of dog food needed in-house.

What I need to do is "freeze" the values in E13, F13, G13, and H13 when the total dogs = 20. If any more dogs are added, I want to add the corresponding number from column I to the total returned when dogs = 20 (sum of E13:H13). The 21st dog would add I4 and the total @ 20. The 22nd would add I5 to the total @ 20 etc.

I hope my babbling did not confuse you any further.

Dave



Of all the things I've lost, I miss my mind the most.

 

[parry]

What the dog numbers running down column D is a mystery but I understand this comment ...

What I need to do is "freeze" the values in E13, F13, G13, and H13 when the total dogs = 20.

To do that code would be used to monitor cell J3, and when it equals 20 then turn the formulas in cells E13, F13, G13, and H13 into actual values. If they are left as a formula they will update, but not if they are just the numbers. To do this a Worksheet_Calculate event would be used to monitor cell J3. Does J3 itself stop at 20 or does it keep increasing?

The next sentence is not clear (to me anyway)...
If any more dogs are added, I want to add the corresponding number from column I to the total returned when dogs = 20 (sum of E13:H13). The 21st dog would add I4 and the total @ 20. The 22nd would add I5 to the total @ 20 etc.
And where are dogs added - in E3:H3? So I4 has 2 so that means that there were 2 more dogs added (ie 20+2) = 22 dogs. Why dont you just have a formula deducting sum E3:H3 from 20. If they were added down column I 1 at the time they would all have 1's in them.

Some things look like a cummulative total but the numbers just dont add up. I cant help feel you may be making something far more complicated than it needs to be.

 

[anubis]

Oaktree's signature has a lot more meaning to me now!

You are right in two areas. First, I am making this more complicated than it needs to be. Second, when you suggest monitoring cell J3 and converting the formulas into their values... To do this a Worksheet_Calculate event would be used to monitor cell J3.... that is exactly what I need. When J3=20, the formulas in E13:H13 need to be converted into values.

I know I can manually do this with PasteSpecial/ Values, but I would like it to be done automatically when J3=20. Would you be able to help with the code?

Does J3 itself stop at 20 or does it keep increasing? It keeps increasing as more dogs are added in E3:H3.

Dave

 

[parry]

Hi Dave, where getting somewhere now. Right click the tab of the sheet and select View Code then paste the following code in the right hand window.

Private Sub Worksheet_Calculate()
Dim Rng As Range, c As Range

Set Rng = Range("E13:H13")
If IsError(Range("J3")) Then GoTo CleanUp
If Range("J3").Value >= 20 Then
    For Each c In Rng
        If c.HasFormula = False Then GoTo CleanUp
    Next c

    For Each c In Rng
        c.Formula = c.Value
    Next c
End If

CleanUp:
Set Rng = Nothing

End Sub

 

[anubis]

Hi Parry. ray: The code works perfectly. ray: I will be able to make the rest of the cells add where and when they should. Thank you for your help and your patience.

Regards,
Dave

 

[anubis]

Just one more thing please, Parry. What does this line of code do?

Dim Rng As Range, c As Range

Dave