Add an additional IF statement with a qualifying column value to existing formulas

wittonlin

Board Regular
Joined
Jan 30, 2016
Messages
144


Example formulas (1)
:


Code:
=IF(ISBLANK('[B]G1-1[/B]'!$BB$1),"",'[B]G1-1'[/B]!$BB$1)

Code:
=IF(ISBLANK('[B]G1-1[/B]'!$BP$1),"",IF('[B]G1-1[/B]'!$BC$1=-1,"",'[B]G1-1'[/B]!$BP$1))


Example formulas (2):

Code:
=IF(ISBLANK('[B]G2-2[/B]'!$BB$1),"",'[B]G2-2[/B]'!$BB$1)

Code:
=IF(ISBLANK([B]'G2-2[/B]'!$BP$1),"",IF('[B]G2-2[/B]'!$BC$1=-1,"",'[B]G2-2[/B]'!$BP$1))


The formulas, as you can see, just display cell values from other sheets, and remain blank if corresponding cell formulas have no value. I need to incorporate another variable and qualifying column, seen below.

i.e. IF Column B (i.e. 'Config'!B:B) is YES of the corresponding value in Column A (i.e. 'Config'!A11:A30), (G1-1, G2-2, G1-3, etc.) then it's TRUE and I need to display the formula value above normally in each cell. IF Column B is NO of the corresponding value in Column A, then do NOT want to display formula value.


Formulas (1):

In this case the sheet code is (G1-1), which matches cell 'A12' and corresponding Column 'B12' = No, so NONE of the values from the formulas (1) above will display. All cells remain blank.


Formulas (2):

In this case the sheet code is (G2-2), which matches cell 'A13' and corresponding Column 'B13' = Yes, so ALL of the values from the formulas (2) above display normally.




Sheet: 'Config'Col ACol B
Row 11
G2-1No
Row 12
G1-1
No
Row 13
G2-2
Yes
Row 14G1-3Yes
Row 15G2-4No
Row 16G2-5No
Row 17G2-6Yes
Row 18G2-7Yes
Row 19G2-8Yes
Row 20G2-3No
Row 21G2-9Yes
Row 22G1-4No
Row 23G1-5No
Row 24
G1-6No
Row 25G1-7No
Row 26
G1-2No
Row 27
G1-8No
Row 28G1-9No
Row 29G1-10Yes
Row 30G2-10

<tbody>
</tbody>





Also posted here.
 
Last edited:

Some videos you may like

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.

istiasztalos

Board Regular
Joined
May 18, 2016
Messages
135
Hi,

How about wrapping a VLOOKUP or INDEX/MATCH around your formulas?

=IF(VLOOKUP("G2-x",Config!A11:B30,2,FALSE)="Yes",original_formula,"")
 

wittonlin

Board Regular
Joined
Jan 30, 2016
Messages
144
Hi,

How about wrapping a VLOOKUP or INDEX/MATCH around your formulas?

=IF(VLOOKUP("G2-x",Config!A11:B30,2,FALSE)="Yes",original_formula,"")

Genius istiasztalos! Spot on the very first try!

Code:
=IF(VLOOKUP("G1-2",Config!A11:B30,2,FALSE)="Yes", IF(ISBLANK('G1-2'!$BB$1),"",'G1-2'!$BB$1), "")
 

Watch MrExcel Video

Forum statistics

Threads
1,122,850
Messages
5,598,441
Members
414,240
Latest member
xnanx

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