Tying data between multiple cells together

Joecam

Member
Joined
May 22, 2014
Messages
41
Reaction score
0
Points
6
Excel Version(s)
365
Hi all,
Hoping someone can help with my question(s). Here's the scenario.

Cell E43 has a data validation list with options "Yes" and "No".
Cell E63 has a manually set value of 1500 and whole number validation that allows it to be set between 1500 and 1,000,000.
Cell D63 corresponds to Cell E63 with the following formula: =IF(E63=1500,0,IF(E63>1500,(((E63-1500)/1000)*21)))

What I'm trying to do is; When E43="Yes", E63=2500 & D63=IF(E63=2500,0,IF(E63>2500,(((E63-2500)/1000)*21)))
E63 also then needs it's whole number validation amended so that the user cannot enter a number lower than 2500.


Right now I'm using a work around which requires the user to manually increase cell E63, but credits out the 21 increase on the line below, but this adds unnecessary complication on the user end that I would like to eliminate.

Any ideas?


Sincerely,
Joe
 

Joecam

Member
Joined
May 22, 2014
Messages
41
Reaction score
0
Points
6
Excel Version(s)
365
So, I adjusted the formula in cell D63 to read:
=+IF(E42="no",IF(E63=1500,0,(((E63-1500)/1000)*21)),IF(E63<2500,0,(((E63-2500)/1000)*21)))

This seems to be accomplishing what I need, formula wise.

However, I still haven't managed to find a way to make default 1500 if E42="No" and change that to 2500 if E42="Yes", while still allowing the cell to be overwriteable with a higher value.
 
Top