Cell Formula Changes When Different Cell Value Changes

[DtTall]

This is a weird one. I don't even know if something like this is
possible. That said, here is my issue.

I have cell J6=205. Now, I want J7 to change based on information in
cell C5.

Here is what I am trying to do.
-The user can enter "LFL" (lot-for-lot) in C5 and then J7=J6. (or some
number, say 0, could be used in place of LFL I guess)
-The user could also enter some integer, say "100" in C5 and then J7
can only be multiples of 100. To cover the 205 in J6, it would need
300 then in J7.
-The Final thing they could enter is something like "100+" in C5 in
which case the minimum vlaue of J7 is 100, but can be anything over
that, so 205 would end up in J7.

Is there any possible way to do this? I know it is a tough problem,
but any help would be great.

Thanks,

DtTall

 

[Brian Erhard]

I might have interpreted your question too literally, but the formula
below works with the example you gave. Enter 0 instead of LFL.

=IF(C5=0,J6,IF(AND(C5>0,C5<>"100+"),(ROUNDUP(J6/C5,0)*C5),IF(C5="100+",IF(J6>100,J6,100))))

 

[jlepack]

Here's the formula for J7.

=IF(C5=0,J6,IF(RIGHT(C5,1) =
"+",IF(J6>INT(MID(C5,1,LEN(C5)-1)),J6,INT(MID(C5,1,LEN(C5)-1))),ROUNDUP(J6/INT(MID(C5,1,LEN(C5))),0)*INT(MID(C5,1,LEN(C5)))))

Cheers,
Jason Lepack

 

[jlepack]

PS.

I assumed that there would always be:
a) a zero (instead of LFL)
b) a number followed by a plus sign
c) a regular number

This you can put any number you want before the "+" and it will
calculate correctly.
J6 = 205 or 9
C5 = "10+"
J7 = 205 or 10

Cheers,
Jason Lepack

 

[DtTall]

Hi Jason,

Thanks for your help. One thing, when using the 100+ I should have
clarified that if there are no requirements in J6, J7 should be zero
and when there are requirement in J6, then the "minimum 100 but
anything over that is ok" should apply. I tried to tease out how to
fix that, but to no avail. Could you help me out once more?

Thanks,

David Schuette
(DtTall)

 

[Jason Lepack]

Here you are,

=IF(C5=0,J6,IF(RIGHT(C5,1)
="+",IF(OR(J6=0,J6=""),0,IF(J6>INT(MID(C5,1,LEN(C5)-1)),J6,INT(MID(C5,1,LEN(C5)-1)))),ROUNDUP(J6/INT(MID(C5,1,LEN(C5))),0)*INT(MID(C5,1,LEN(C5)))))

That should do it!

Cheers,
Jason Lepack