# Entering True/False in one cell based on True/False criteria from multiple cells and creating macro to automate

#### campos1578

##### New Member
Hey Team,

I have set up conditional true/false criteria for evaluating if something will fit (Max dimensions allowable are shown in color). In my example below, I've taken the first line item on the left and and kept the weight but evaluated the other dimensions (width,height,length) in different orders to see if they will fit. I want the cell next to "Will it Fit" (Q6) to display "Yes" if all dimensions = "True" from Q3,Q4,Q5 or "False" if even one dimension criteria in Q3,Q4,Q5 = "False".

Formula for "True"/"False" value: =IF(AND(M2<=3.9, N2<=9.7,O2<=5.8, P2<=9.7), "True","False")

Question 1 - How do I get Q6 to evaluate the aforementioned criteria and display "Yes" or "No"?
Question 2 - How can I automate this process for the rest of my lines?

Thanks for the help!
-Jonathan

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### Dave Patton

##### Well-known Member
You can post an extract of your sheet with the forum's XL2BB.
Try either of the suggested versions of your formula.
T202102a.xlsm
LMNOPQ
1WeightWidthHeightDepth
23.99.75.89.7TRUE
31.665.98.755.9FALSE
4or1.665.98.755.9FALSE
53959TRUE
1d
Cell Formulas
RangeFormula
Q2:Q3Q2=AND(M2<=3.9, N2<=9.7,O2<=5.8, P2<=9.7)
Q4:Q5Q4=AND(M4<=\$M\$2,N4<=\$N\$2,O4<=\$O\$2, P4<=\$P\$2)

#### campos1578

##### New Member
You can post an extract of your sheet with the forum's XL2BB.
Try either of the suggested versions of your formula.
T202102a.xlsm
LMNOPQ
1WeightWidthHeightDepth
23.99.75.89.7TRUE
31.665.98.755.9FALSE
4or1.665.98.755.9FALSE
53959TRUE
1d
Cell Formulas
RangeFormula
Q2:Q3Q2=AND(M2<=3.9, N2<=9.7,O2<=5.8, P2<=9.7)
Q4:Q5Q4=AND(M4<=\$M\$2,N4<=\$N\$2,O4<=\$O\$2, P4<=\$P\$2)

I'm not following from your response and not able to upload that particular document type, but here are more details of my intentions.

In this example, the weight is the constant and the the width, height, depth are in different combinations. How can I do this for the other line Items?

-Jonathan

#### Attachments

• 1612749997473.png
42.6 KB · Views: 0
• 1612751865321.png
53.6 KB · Views: 0

#### Dave Patton

##### Well-known Member
What did you try?
You can download my post. Click on the icon below the F(x) and paste to a clean sheet.
Why not use the suggested formula?
The formula yields True or False. Is it necessary to convert to Yes or No?
Check the forum for help installing the XL2BB. We are volunteers. We often can provide better and more
focused suggestions if we have an extract with clear examples of the challenge.

#### Peter_SSs

##### MrExcel MVP, Moderator

Check the forum for help installing the XL2BB.
Dave, FYI (it may change soon but) currently XL2BB is only available for Excel 365 on MacOS. Version info is listed at the top of this page.

#### campos1578

##### New Member
Success!

Apologies for the delayed response Dave. I used the formulas and indeed it worked.

Next Steps:
 Weight Width Height Length (Depth) True/False 3.9​ 9.7​ 5.8​ 9.7​ TRUE​ 1.66​ 5.9​ 8.75​ 5.9​ FALSE​ 1.66​ 8.78​ 5.9​ 5.9​ FALSE​ 1.66​ 5.9​ 5.9​ 8.78​ FALSE​ Q6 Will if Fit?

My next step is for cell Q6 to show "True" or "False" based on "True/False" Values from Q3:Q5.

How do make this an automated process?

What did you try?
You can download my post. Click on the icon below the F(x) and paste to a clean sheet.
Why not use the suggested formula?
The formula yields True or False. Is it necessary to convert to Yes or No?
Check the forum for help installing the XL2BB. We are volunteers. We often can provide better and more
focused suggestions if we have an extract with clear examples of the challenge.

#### Attachments

• 1612977020848.png
19.3 KB · Views: 0

#### campos1578

##### New Member

Success!

Apologies for the delayed response Dave. I used the formulas and indeed it worked.

Next Steps:
 Weight Width Height Length (Depth) True/False 3.9​ 9.7​ 5.8​ 9.7​ TRUE​ 1.66​ 5.9​ 8.75​ 5.9​ FALSE​ 1.66​ 8.78​ 5.9​ 5.9​ FALSE​ 1.66​ 5.9​ 5.9​ 8.78​ FALSE​ Q6 Will if Fit?

My next step is for cell Q6 to show "True" or "False" based on "True/False" Values from Q3:Q5.

How do make this an automated process?
@Dave Patton
I found a formula, tested it, and it works for Q6 : =IF(COUNTIF(Q3:Q5, "True")=3, "Yes","No")

How do I automate the whole sequence?

#### Dave Patton

##### Well-known Member
Try the Sumproduct

T202102a.xlsm
LMNOPQR
1WeightWidthHeightDepth
2Criteria3.99.75.89.7
31.669.65.555.9TRUE
43.89.55.79.6TRUE
5or1.669.65.555.9TRUE
63959TRUE
7TRUETRUE
1d
Cell Formulas
RangeFormula
Q3:Q6Q3=AND(M3<=\$M\$2,N3<=\$N\$2,O3<=\$O\$2, P3<=\$P\$2)
Q7Q7=COUNTIF(Q3:Q6,TRUE)=4
R7R7=SUMPRODUCT(--(M3:P6<=M2:P2))=COUNT(M3:P6)

#### campos1578

##### New Member
Thanks @Dave Patton
I'm wanting the cell in F2 to copy to M2,M3,M4 and the cells in G2,H2,I2 to copy and paste in different sequences in N3,4,5/ O3,4,5/ P3,4,5

I'm using the following macro to accomplish this but want it to loop through the rest of the rows ranging from F to J. In addition I'm setting the Value at K2 to Q6.

How can I run the macro below to accomplish this?

Sub Will_It_Fit()
'
' Will_It_Fit Macro
'

'
Range("F2").Select
Selection.Copy
Range("M3").Select
ActiveSheet.Paste
Range("M4").Select
ActiveSheet.Paste
Range("M5").Select
ActiveSheet.Paste
Range("G2").Select
Application.CutCopyMode = False
Selection.Copy
Range("N3").Select
ActiveSheet.Paste
Range("H2").Select
Application.CutCopyMode = False
Selection.Copy
Range("O3").Select
ActiveSheet.Paste
Range("I2").Select
Application.CutCopyMode = False
Selection.Copy
Range("P3").Select
ActiveSheet.Paste
Range("N4").Select
ActiveSheet.Paste
Range("G2").Select
Application.CutCopyMode = False
Selection.Copy
Range("O4").Select
ActiveSheet.Paste
Range("H2").Select
Application.CutCopyMode = False
Selection.Copy
Range("P4").Select
ActiveSheet.Paste
Range("N5").Select
ActiveSheet.Paste
Range("I2").Select
Application.CutCopyMode = False
Selection.Copy
Range("O5").Select
ActiveSheet.Paste
Range("G2").Select
Application.CutCopyMode = False
Selection.Copy
Range("P5").Select
ActiveSheet.Paste
End Sub

,

Replies
23
Views
2K
Replies
3
Views
174
Replies
0
Views
229
Replies
0
Views
168
Replies
11
Views
8K

1,129,687
Messages
5,637,832
Members
416,984
Latest member
dee10

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back