0

After a bit of consternation I managed to come up with a pretty simple solution to highlight cells in a range if a cell in a different range has the same value:

=MATCH(RC,'Interactive Calendar'!C2,0)>0

What I'd like to do next, and I'm having some difficulty figuring it out, is to check if C3 in the Interactive Calendar sheet has a specific value based around location.

So IF =MATCH(RC,'Interactive Calendar'!C2,0)>0 AND the corresponding cell in 'Interactive Calendar'!C3="Brisbane", then format the cell. There are three locations, Melbourne, Brisbane and Sydney so the plan was to have 3 different formatting rules across the same area.

Improve this question
2
  • Are you using R1C1 as your setup? Are your columns Numbers or Letters? Commented Jan 7, 2020 at 22:08
  • @Scott Craner I'm using R1C1 as that was the simplest way to use the Active Cell as part of the formula. Commented Jan 7, 2020 at 22:09

1 Answer 1

Reset to default
1

Use:

=AND(ISNUMBER(MATCH(RC,'Interactive Calendar'!C2,0)),INDEX('Interactive Calendar'!C3,MATCH(RC,'Interactive Calendar'!C2,0))='Brisbane")
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks Scott - didn't quite get the required result though. It is matching dates and not numbers though if that affects the formula?
Nothing happened. It's not applying the formatting to the cell with the second condition.
If that does not work, make sure your dates are: 1. True dates and not text. 2. That they do not include a time portion to the data that is being masked by the format.
Fantastic, that appears to have worked. I'll run a few more tests and then give it the tick.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.