Using if/and formula

[Jaclync]

I am trying to create a worksheet to be used to fill in information for jobs our company does. I want to have it so that if you enter a number into one cell and it meets a specification (set to the cell) then another cell will automatically fill in.

Example: if cell B31=900, and cell F8= any number between 10-12, then cell B5 will auto fill 70.

Thank you in advance for your help.

 

[repairman615]

Hello,

Welcome to the Board!

Try this in cell B5:

<TABLE style="WIDTH: 284pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=378><COLGROUP><COL style="WIDTH: 284pt; mso-width-source: userset; mso-width-alt: 6912" width=378><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #eaf1dd; WIDTH: 284pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=378>=IF(AND(B31=900,AND(F8>=10,F8<=12)),70,"")</TD></TR></TBODY></TABLE>

 

[Jaclync]

Great. Thank you!

 

[Scott Huish]

I don't know why you would need 2 ANDS:

=IF(AND(B31=900,F8>=10,F8<=12),70,"")

 

[repairman615]

=IF(AND(B31=900,F8>=10,F8<=12),70,"")

ps, thanks again HOTPEPPER for the Ubound tip... used it a few times.

 

[Jaclync]

I appreciate all the help. I do have another extension to this question. Using the same/similar formula...
I need the option of other possible outcomes.

example:
if b31=900 and f8= any number between 10-12 then b5=70.

I also need to know if it is possible to have, on the same worksheet, other options. Meaning b31 can have other possible entries (example b31 can be 900, 1800, or 3600) all of these numbers will have different outcomes in f8 and b5.
Example: If b31=900 and f8=a number between 10-12 then b5=70, if b31=1800 and f8=any number between 23-25 then b5=70.
The higher b31 gets the more possibilities there are for f8 and b5.
Is this too long of a process? Is there another option to make this less complicated?

Thank you for the help.

 

[repairman615]

You could hard code it like:

<TABLE style="WIDTH: 377pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=502><COLGROUP><COL style="WIDTH: 377pt; mso-width-source: userset; mso-width-alt: 9179" width=502><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d7e4bc; WIDTH: 377pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=502>=IF(AND(OR(B31=900,B31=1800,B31=3600),F8>=10,F8<=12),70,"")

</TD></TR></TBODY></TABLE>

or use another cell to reference the value in B31, for example let c31 house the value you choose (900, 1800, 3600....)
like:
<TABLE style="WIDTH: 377pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=502><COLGROUP><COL style="WIDTH: 377pt; mso-width-source: userset; mso-width-alt: 9179" width=502><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d7e4bc; WIDTH: 377pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=502>=IF(AND(B31=C31,F8>=10,F8<=12),70,"")

</TD></TR></TBODY></TABLE>

 

[repairman615]

What are the other possibilities for F8 and B5 result?

 

[Jaclync]

Great. I will give that a try. I was hoping for something like that.

Thank you.

 

[Jaclync]

Example: if B31=1800 and f8= 23-25 then b5=70
still using b31=1800 f8=28-30 then b5=59.05 or f8=34-36 then b5=46.36
It continues with two other possibilities similar to this. Then when you get to b31=3600 there is a possible 9 ranges for f8 resulting in 9 outcomes for b5.