I wanted to format a cell based on another cell values in another sheet,
this formula works but only on the certain cell it was input first:
OR(indirect("2019!U18")="MIS",indirect("2019!u18")="Purchasing")
dragging or copying cell formats does not help as it returns the value of u18 only, unlike in excel where it will return the value of the next cell (u19) when dragged or copied.
Are there modifications in that formula or other functions that I can use to achieve the same result?
Best Answer
The reason that
INDIRECT
(as it is used in your formula) does NOT copy down is because it is a "cell reference, written as a string" Ref.There are many alternatives. This example
OFFSET
Doc Ref as an alternative.=OR(offset(wa_139135_2019!U18,0,0)="MIS",offset(wa_139135_2019!U18,0,0)="Purchasing")