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

[wittonlin]

Example formulas (1):

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

=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):

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

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




[TABLE="class: grid, width: 250, align: left"]
<tbody>[TR]
[TD="width: 64, align: center"]Sheet: 'Config'[/TD]
[TD="width: 64, align: center"]Col A[/TD]
[TD="width: 64, align: center"]Col B[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]Row 11
[/TD]
[TD="width: 64, align: center"]G2-1[/TD]
[TD="width: 64, align: center"]No[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]Row 12
[/TD]
[TD="width: 64, align: center"]G1-1
[/TD]
[TD="width: 64, align: center"]No
[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]Row 13
[/TD]
[TD="width: 64, align: center"]G2-2
[/TD]
[TD="width: 64, align: center"]Yes
[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]Row 14[/TD]
[TD="width: 64, align: center"]G1-3[/TD]
[TD="width: 64, align: center"]Yes[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]Row 15[/TD]
[TD="width: 64, align: center"]G2-4[/TD]
[TD="width: 64, align: center"]No[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]Row 16[/TD]
[TD="width: 64, align: center"]G2-5[/TD]
[TD="width: 64, align: center"]No[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]Row 17[/TD]
[TD="width: 64, align: center"]G2-6[/TD]
[TD="width: 64, align: center"]Yes[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]Row 18[/TD]
[TD="width: 64, align: center"]G2-7[/TD]
[TD="width: 64, align: center"]Yes[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]Row 19[/TD]
[TD="width: 64, align: center"]G2-8[/TD]
[TD="width: 64, align: center"]Yes[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]Row 20[/TD]
[TD="width: 64, align: center"]G2-3[/TD]
[TD="width: 64, align: center"]No[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]Row 21[/TD]
[TD="width: 64, align: center"]G2-9[/TD]
[TD="width: 64, align: center"]Yes[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]Row 22[/TD]
[TD="width: 64, align: center"]G1-4[/TD]
[TD="width: 64, align: center"]No[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]Row 23[/TD]
[TD="width: 64, align: center"]G1-5[/TD]
[TD="width: 64, align: center"]No[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]Row 24
[/TD]
[TD="width: 64, align: center"]G1-6[/TD]
[TD="width: 64, align: center"]No[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]Row 25[/TD]
[TD="width: 64, align: center"]G1-7[/TD]
[TD="width: 64, align: center"]No[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]Row 26
[/TD]
[TD="width: 64, align: center"]G1-2[/TD]
[TD="width: 64, align: center"]No[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]Row 27
[/TD]
[TD="width: 64, align: center"]G1-8[/TD]
[TD="width: 64, align: center"]No[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]Row 28[/TD]
[TD="width: 64, align: center"]G1-9[/TD]
[TD="width: 64, align: center"]No[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]Row 29[/TD]
[TD="width: 64, align: center"]G1-10[/TD]
[TD="width: 64, align: center"]Yes[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]Row 30[/TD]
[TD="width: 64, align: center"]G2-10[/TD]
[/TR]
</tbody>[/TABLE]





Also posted here.

 

[istiasztalos]

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]

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!

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