0

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?

0

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.