Q:

Excel - Formula reference to cell on the same row

I'm trying to build a simple formula: if the cell on the same row as current cell, but column J is either =1 or empty, then the result is 1, else 0.
The part about =1 works, the part about="" does not for some reason.
Here is my formula:
    =IF(OR("J"&ROW()=1,"J"&ROW()=""),1,0)

Can anyone help me find out why "J"&ROW()="" returns false, even if it is clearly true? The "J"&ROW()=1 returns true if the target cell is 1.
Another thing i tested is "J"&ROW()=j50, where 50 is the actual row number, and this also returned false, which does not make any sense to me.

A:

You need either INDIRECT to turn the string into a cell reference
=IF(OR(INDIRECT("J"&ROW())=1,INDIRECT("J"&ROW())=""),1,0)

or use INDEX (as INDIRECT is volatile)
=IF(OR(INDEX(J:J,ROW())=1,INDEX(J:J,ROW())=""),1,0)

