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

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(C50,C5<"100+"),(ROUNDUP(J6/C5,0)*C5),IF(C5="100+",IF(J6100,J6,100))))



DtTall wrote:
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

Here's the formula for J7.

=IF(C5=0,J6,IF(RIGHT(C5,1) =
"+",IF(J6INT(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

DtTall wrote:
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

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

jlepack wrote:
Here's the formula for J7.

=IF(C5=0,J6,IF(RIGHT(C5,1) =
"+",IF(J6INT(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

DtTall wrote:
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

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)


On Nov 16, 1:52 pm, "jlepack" wrote:
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

jlepack wrote:
Here's the formula for J7.

=IF(C5=0,J6,IF(RIGHT(C5,1) =
"+",IF(J6INT(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

DtTall wrote:
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

Here you are,

=IF(C5=0,J6,IF(RIGHT(C5,1)
="+",IF(OR(J6=0,J6=""),0,IF(J6INT(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

DtTall wrote:
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)


On Nov 16, 1:52 pm, "jlepack" wrote:
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

jlepack wrote:
Here's the formula for J7.

=IF(C5=0,J6,IF(RIGHT(C5,1) =
"+",IF(J6INT(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

DtTall wrote:
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