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

[campos1578]

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

 

[Dave Patton]

You can post an extract of your sheet with the forum's XL2BB.
Try either of the suggested versions of your formula.

T202102a.xlsm
	L	M	N	O	P	Q
1		Weight	Width	Height	Depth
2		3.9	9.7	5.8	9.7	TRUE
3		1.66	5.9	8.75	5.9	FALSE
4	or	1.66	5.9	8.75	5.9	FALSE
5		3	9	5	9	TRUE
1d

Cell Formulas
Range	Formula
Q2:Q3	Q2	=AND(M2<=3.9, N2<=9.7,O2<=5.8, P2<=9.7)
Q4:Q5	Q4	=AND(M4<=$M$2,N4<=$N$2,O4<=$O$2, P4<=$P$2)

 

[campos1578]

You can post an extract of your sheet with the forum's XL2BB.
Try either of the suggested versions of your formula.

T202102a.xlsm
	L	M	N	O	P	Q
1		Weight	Width	Height	Depth
2		3.9	9.7	5.8	9.7	TRUE
3		1.66	5.9	8.75	5.9	FALSE
4	or	1.66	5.9	8.75	5.9	FALSE
5		3	9	5	9	TRUE
1d

Cell Formulas
Range	Formula
Q2:Q3	Q2	=AND(M2<=3.9, N2<=9.7,O2<=5.8, P2<=9.7)
Q4:Q5	Q4	=AND(M4<=$M$2,N4<=$N$2,O4<=$O$2, P4<=$P$2)

Thanks for the quick reply!

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?

Thanks and I look forward to your reply!
-Jonathan

 

[Dave Patton]

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]

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]

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.

 

[campos1578]

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]

Try the Sumproduct

T202102a.xlsm
	L	M	N	O	P	Q	R
1		Weight	Width	Height	Depth
2	Criteria	3.9	9.7	5.8	9.7
3		1.66	9.6	5.55	5.9	TRUE
4		3.8	9.5	5.7	9.6	TRUE
5	or	1.66	9.6	5.55	5.9	TRUE
6		3	9	5	9	TRUE
7						TRUE	TRUE
1d

Cell Formulas
Range	Formula
Q3:Q6	Q3	=AND(M3<=$M$2,N3<=$N$2,O3<=$O$2, P3<=$P$2)
Q7	Q7	=COUNTIF(Q3:Q6,TRUE)=4
R7	R7	=SUMPRODUCT(--(M3:P6<=M2:P2))=COUNT(M3:P6)

 

[campos1578]

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


,