Custom Formula Conditional Formatting in Google sheets, referencing to different sheet

Question

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?

Answer 1

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")