If a cell in column is blank when a cell same row is not blank

[kcmuppet]

I'm trying to set up a warning when a something is entered in the [Source] column of myTable, but the cell in the same row in the [Reference] column is left empty.

This works:

=IF(
    COUNTA(
        FILTER(myTable[[Source]:[Reference]],(
            (NOT(ISBLANK(myTable[Source])))*
            (ISBLANK(myTable[Reference]))
            )
        )
    )>0,
    "Warning","")

...but is there a better (more efficient) way (without VBA)?

 

[Coyotex3]

Would the formula live in a column within the table or outside of the table? If outside of the table maybe you can insert this formula and drag it down.

=IF(AND(Table1[@Source]<>"",Table1[@Reference]=""),"Warning","")

 

[kcmuppet]

Would the formula live in a column within the table or outside of the table? If outside of the table maybe you can insert this formula and drag it down.

=IF(AND(Table1[@Source]<>"",Table1[@Reference]=""),"Warning","")

Thanks, I'm looking for something that works in one cell outside the table, rather than a helper column. In the working formula I created the need to filter the whole table before counting it seems to be a bit unwieldy, but maybe it's more efficient than looking in each row.

Maybe even filtering only one column is a bit more efficient, i.e.:

=IF(
    COUNTA(
        FILTER(myTable[Source],(
            (NOT(ISBLANK(myTable[Source])))*
            (ISBLANK(myTable[Reference]))
            )
        )
    )>0,
    "Warning","")

 

[Fluff]

I would be surprised if your formula worked, but another option is

=IF(COUNTIFS(MyTable[Source],"<>",MyTable[Reference],""),"Warning","")

 

[Coyotex3]

@Fluff is "<>" another way of saying<>""? First time I've seen this one !

 

[Fluff]

It's just saying Isn't blank/empty

 

[kcmuppet]

I would be surprised if your formula worked,

You're right - it didn't work properly!

but another option is...

Thanks

 

[Fluff]

Glad we could help & thanks for the feedback.