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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

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,118,662
Messages
5,573,484
Members
412,533
Latest member
Pejter
Top