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
12
Office Version
  1. 2019
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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,679
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Jan 3, 2020
Messages
12
Office Version
  1. 2019
Platform
  1. MacOS
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: 0
  • 1612751865321.png
    1612751865321.png
    53.6 KB · Views: 0

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,679
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
48,603
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jan 3, 2020
Messages
12
Office Version
  1. 2019
Platform
  1. MacOS
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: 0

campos1578

New Member
Joined
Jan 3, 2020
Messages
12
Office Version
  1. 2019
Platform
  1. MacOS

ADVERTISEMENT

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?
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,679
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Jan 3, 2020
Messages
12
Office Version
  1. 2019
Platform
  1. MacOS
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


,
 

Watch MrExcel Video

Forum statistics

Threads
1,130,424
Messages
5,642,041
Members
417,251
Latest member
Dordrecht

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
Top