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

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


    [TABLE="align: left, border: 1, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 64"]Sheet: 'Config'[/TD]
    [TD="width: 64"]Col A[/TD]
    [TD="width: 64"]Col B[/TD]

    [/tr]


    [tr]


    [td]

    Row 11

    [/td]


    [td]

    G2-1

    [/td]


    [td]

    No

    [/td]


    [/tr]


    [tr]


    [td]

    Row 12

    [/td]


    [td]

    G1-1

    [/td]


    [td]

    No

    [/td]


    [/tr]


    [tr]


    [td]

    Row 13

    [/td]


    [td]

    G2-2

    [/td]


    [td]

    Yes

    [/td]


    [/tr]


    [tr]


    [td]

    Row 14

    [/td]


    [td]

    G1-3

    [/td]


    [td]

    Yes

    [/td]


    [/tr]


    [tr]


    [td]

    Row 15

    [/td]


    [td]

    G2-4

    [/td]


    [td]

    No

    [/td]


    [/tr]


    [tr]


    [td]

    Row 16

    [/td]


    [td]

    G2-5

    [/td]


    [td]

    No

    [/td]


    [/tr]


    [tr]


    [td]

    Row 17

    [/td]


    [td]

    G2-6

    [/td]


    [td]

    Yes

    [/td]


    [/tr]


    [tr]


    [td]

    Row 18

    [/td]


    [td]

    G2-7

    [/td]


    [td]

    Yes

    [/td]


    [/tr]


    [tr]


    [td]

    Row 19

    [/td]


    [td]

    G2-8

    [/td]


    [td]

    Yes

    [/td]


    [/tr]


    [tr]


    [td]

    Row 20

    [/td]


    [td]

    G2-3

    [/td]


    [td]

    No

    [/td]


    [/tr]


    [tr]


    [td]

    Row 21

    [/td]


    [td]

    G2-9

    [/td]


    [td]

    Yes

    [/td]


    [/tr]


    [tr]


    [td]

    Row 22

    [/td]


    [td]

    G1-4

    [/td]


    [td]

    No

    [/td]


    [/tr]


    [tr]


    [td]

    Row 23

    [/td]


    [td]

    G1-5

    [/td]


    [td]

    No

    [/td]


    [/tr]


    [tr]


    [td]

    Row 24

    [/td]


    [td]

    G1-6

    [/td]


    [td]

    No

    [/td]


    [/tr]


    [tr]


    [td]

    Row 25

    [/td]


    [td]

    G1-7

    [/td]


    [td]

    No

    [/td]


    [/tr]


    [tr]


    [td]

    Row 26

    [/td]


    [td]

    G1-2

    [/td]


    [td]

    No

    [/td]


    [/tr]


    [tr]


    [td]

    Row 27

    [/td]


    [td]

    G1-8

    [/td]


    [td]

    No

    [/td]


    [/tr]


    [tr]


    [td]

    Row 28

    [/td]


    [td]

    G1-9

    [/td]


    [td]

    No

    [/td]


    [/tr]


    [tr]


    [td]

    Row 29

    [/td]


    [td]

    G1-10

    [/td]


    [td]

    Yes

    [/td]


    [/tr]


    [tr]


    [td]

    Row 30

    [/td]


    [td]

    G2-10

    [/td]


    [td]

    Yes

    [/td]


    [/tr]


    [/TABLE]

  • Code
    =IF(VLOOKUP("G2-[I]x[/I]",Config!A11:B30,2,FALSE)="Yes",[I]original_formula[/I],"")


    Code
    =IF(VLOOKUP("G1-2",Config!A11:B30,2,FALSE)="Yes", IF(ISBLANK('G1-2'!$BB$1),"",'G1-2'!$BB$1), "")
  • Is your query resolved then?

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!