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

campos1578

New Member
Joined
Jan 3, 2020
Messages
22
Office Version
  1. 365
Platform
  1. MacOS
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")
1612735650693.png


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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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)
 
Upvote 0
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)
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.

1612751892348.png

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
 

Attachments

  • 1612749997473.png
    1612749997473.png
    42.6 KB · Views: 4
  • 1612751865321.png
    1612751865321.png
    53.6 KB · Views: 4
Upvote 0
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.
 
Upvote 0
Success!

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

Next Steps:
WeightWidthHeightLength (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​
Q6Will 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
    1612977020848.png
    19.3 KB · Views: 4
Upvote 0
Success!

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

Next Steps:
WeightWidthHeightLength (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​
Q6Will 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?
 
Upvote 0
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)
 
Upvote 0
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
1612991480487.png


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


,
 
Upvote 0

Forum statistics

Threads
1,223,889
Messages
6,175,223
Members
452,620
Latest member
dsubash

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top