I apologize for the vague title. but I could not think of a way to properly explain the problem in a single line with so few characters... So I was hoping to pull in the conditional formatting experts who can tell me if it is even possible to accomplish what I am trying to do here!
I am using Microsoft Office Professional Plus 2010.
I have two arrays that look like this:
The column A shows the "ID" of a task.
Column B shows the completion date of the task
I want to be able to automatically highlight the "Actual" array IDs and dates, according to if the "Actual" is matching ( or greater/less than) the "Proposal". Also if there is no matching task ID in proposal.
- Red if there is no matching task ID in the proposal array
- Orange if the Actual date is later than the proposal date
- Dark green if the Actual date is the same as the proposal date
- Light green if the Actual date is earlier than the proposal date.
Is this possible? I am struggling with how to get conditional formating to search an array, match the ID, then apply the formatting based on a different cell in the same row of the matching ID.
Any help here would be greatly appreciated!
To help visualise the needed automatic formatting:
I will also edit this post in case I managed to resolve it myself. Thanks for your time.
No need to use the syntax you have used there, CF only works on TRUE/FALSE (or 1/0) so you just need to construct a formula that will return 1 of those....
=IF(ISERROR(VLOOKUP($A3,$A$10:$B$12,2,0)),TRUE,FALSE)
becomes...
=VLOOKUP($A3,$A$10:$B$12,2,0)
etc
Also, to just find the ID...
=MATCH($A3,$A$10:$B$12,0)
- Posts
-
33
- Registration date
- Sunday June 19, 2016
- Status
- Contributor
- Last seen
- November 20, 2016
1=IF(ISERROR(VLOOKUP($A3,$A$10:$B$12,2,0)),TRUE,FALSE)
becomes...
=VLOOKUP($A3,$A$10:$B$12,2,0)
etc
Also, to just find the ID...
=MATCH($A3,$A$10:$B$12,0)
- Posts
-
2744
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- May 10, 2021
461