Forum Discussion

DennisMetro's avatar
DennisMetro
Brass Contributor
Jul 02, 2021
Solved

Excel formula

1.      =IF((C10="")*(D10=""),"",IF(C10>D10,$B$10,$E$10)) this formula correctly fills G16 with either B10 or E10 data. 2.     G17 contains a graphic which is linked to a named range and which works...
community
  • SergeiBaklan's avatar
    SergeiBaklan
    Jul 05, 2021

    DennisMetro 

    You may add named formulas for each cell with flag, e.g. for L17

    =XLOOKUP('Linked Picture'!$L$16, 'Linked Picture'!$Z$31:$Z$46, 'Linked Picture'!$AA$31:$AA$46,'Linked Picture'!$AA$47)

    and for O17

    =XLOOKUP('Linked Picture'!$O$16, 'Linked Picture'!$Z$31:$Z$46, 'Linked Picture'!$AA$31:$AA$46,'Linked Picture'!$AA$47)
mtarler's avatar
mtarler
Silver Contributor
Jul 02, 2021
maybe it is just a typo when you typed it here but you said the formula that is failing is:
=IF(G16="",""),FlagLookup9
but correct format is
=IF(G16="","",FlagLookup9)
DennisMetro's avatar
DennisMetro
Brass Contributor
Jul 02, 2021
Thanks for you suggestion. When I copy/paste your function I get the same message.
  • mtarler's avatar
    mtarler
    Silver Contributor
    Jul 02, 2021
    I don't know what FlagLookup9 is and thought you were using it as reference to the upper equation but then based on your response ...
    Attaching the file (remove any confidential/personal info) would help a lot.
    But if the equation:
    =INDEX('Linked Picture'!$AA$31:$AA$46, MATCH('Linked Picture'!$G$16,'Linked Picture'!$Z$31:$Z$46,0))
    worked and now you just want to show blank if G16 is blank then
    =IF(G16="","",INDEX('Linked Picture'!$AA$31:$AA$46, MATCH('Linked Picture'!$G$16,'Linked Picture'!$Z$31:$Z$46,0)))
    should do it.
    • DennisMetro's avatar
      DennisMetro
      Brass Contributor
      Jul 02, 2021

      mtarler Hi.

      Thanks again. I tried that but got no joy.

      I'm guessing it may not be possible.

      At any rate the file is attached. 

      Thanks for your time. Dennis

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jul 03, 2021

        DennisMetro 

        As named formula you need to use any function which returns reference, that could be OFFSET, INDEX or XLOOKUP.

        If XLOOKUP is available for you Excel, you may handle empty cells with it. First, add dummy picture at the end of your range with flags (in general it could be in any other place), like

        I take blue to make it visible, after testing it could be change on any transparent shape of any form.

        Next, add named formula as

        =XLOOKUP('Linked Picture'!$O$28, 'Linked Picture'!$Z$31:$Z$46, 'Linked Picture'!$AA$31:$AA$46,'Linked Picture'!$AA$47)

        Last parameters indicates what to return if nothing is found. I named it GetFlag. If country exists it gives

        If empty cell with country name

        Please check in attached file.

Resources