0

Let's say I have multiple formulas on different pages of the spreadsheet like this:

=ARRAYFORMULA(IFS(
O2:O="","",
O2:O="VOID","",
L2:L=1,IF(P2:P>Q2:Q,"GREEN","RED"),
L2:L="X",IF(P2:P=Q2:Q,"GREEN","RED"),
L2:L=2,IF(P2:P<Q2:Q,"GREEN","RED"),
J2:J="More than 2.5 goals",IF(P2:P+Q2:Q>2.5,"GREEN","RED")))

Is there a way to use Control + H to change all GREEN to RED and all RED to GREEN at once?

Something like I could do like what would happen if I used SUBSTITUTE on a string:

=SUBSTITUTE(SUBSTITUTE(RANGE,"GREEN","RED"),"RED","GREEN")

1 Answer 1

0

There is a logical problem with what you are suggesting. If you were to actually try your SUBSTITUTE formula, you would see that everything would wind up GREEN in the end, because you would have changed all GREEN to RED first (which would have left everything RED) and then changed all RED (i.e., everything) back to GREEN.

You can use Find/Replace. But you'll need to do it in three steps:

1.) Change GREEN to, say, HOLD.

2.) Change RED to GREEN.

3.) Change HOLD to RED.

Since the Find/Replace dialog window stays open, you should be able to do this for an entire spreadsheet in about 15 seconds.

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.