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:

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), "")
 

Forum statistics

Threads
1,081,802
Messages
5,361,388
Members
400,628
Latest member
ganeshkhatri

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top