Conditional Formula Needed

Dsheaff

New Member
Joined
Aug 14, 2018
Messages
17
I need the action identified below to take place only if the value posted to cell AK8 appearing on the Worksheet identified as "QC Summary Rail" is equal to or greater than 1. Otherwise, the recipient cell is to remain blank.

='Candidate Selection'!P4
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
Break apart the formula into new formulas, to see what each one returns.

So enter these four formulas in any blank cells on the sheet where this big formula is located, and let us know what each one returns:
='QC W Beam'!O8=1
='QC W Beam'!I7="GUARDRAIL REPAIR (W BEAM & THRIE BEAM)"
='QC W Beam'!M200="X"
='QC W Beam'!G199
 

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".

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,792
Office Version
  1. 365
  2. 2010
If you use XL2BB to post significant portions of your sheet, it would help us. Or just follow Joe4's instruction.
 

Dsheaff

New Member
Joined
Aug 14, 2018
Messages
17
Again, I offer the following:

=IF(AND('QC W Beam'!O8=1,'QC W Beam'!I7="GUARDRAIL REPAIR (W & THRIE BEAM)",'QC W Beam'!M30="X"),'QC W Beam'!G29,"")

I modified it slightly....I originally misrepresented a portion of the formula...I changed (W BEAM & THRIE BEAM) to (W & THRIE BEAM) and placed the formula in a cell where I knew there should be a return. When entered, no error messages were received, however, the recipient cell in which it was posted remained blank. All of the conditions are met but there should have been a return. I'm wondering about the last portion of the formula.... 'QC W Beam'!M30="X"),'QC W Beam'!G29,"")... Originally, When it stood by itself with no conditions it was preceded with =IF and returned the expected data to the recipient cell in which it was posted.

The expected data referenced above is the value in QC W Beam cell G29
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,792
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

In one case, you're checking M200 and in another M30, then in one case you're checking G199 and the other G29.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
Please pay special attention to what you are posting here. If you keep "misrepresenting" things, the formulas will never work. All that we have to work off if what you share with us here. So we need to count on that data being accurate.

Basically, what we are building is an IF statement with an AND clause. Its structure looks like this:
=IF(AND(condition1,condition2,condition3),'QC W Beam'!G29,"")

If condition1, condition2, and condition3 all return TRUE, then the formula HAS to return the value from 'QC W Beam'!G29.
So, if it is not working the way you expect, then pull out each condition (like I just had you do above), and see if each condition is True or not.

I would also recommend temporarily changing the last argument of the IF function to something other than "", maybe with something like "conditions not met".
That way, you will be able to tell the difference between the IF conditions not being met versus them being met, but the cell you are bringing back is empty.
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,792
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

For what it's worth, select each condition (one at a time) and touch F9 on your keyboard to see how it evaluates.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
For what it's worth, select each condition (one at a time) and touch F9 on your keyboard to see how it evaluates.
That's pretty cool. I never knew about that.
I did discover, however, that after doing that, you want to hit ESC to leave the formula. If you hit ENTER, it overwrites your condition with the Boolean value!
 

Dsheaff

New Member
Joined
Aug 14, 2018
Messages
17
Thanks guys... I got it! Amazing how long you can stare at something and not see it. I did what you recommended above and found a missing comma. I also discovered the issue with using ESC to leave the formula.

Thanks again
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,792
Office Version
  1. 365
  2. 2010
Great, Dsheaff...sorry I didn't point out the ESC key requirement. Glad it's working.

Joe4: you just made me smile when you said you never knew that!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,192
Messages
5,640,766
Members
417,165
Latest member
Hilders1

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