Whilst there are a number of similar examples on here, none quite hit the nail for my problem.
Each month I am required to compare a course catalogue, identifying any new courses that have been added and any that have been deleted. The worksheet is exported from a SharePoint site into Excel where it is sorted alphabetically by course title. The following formula is then applied in the conditional formatting argument field as a Cell Value, equal to:
=VLOOKUP(A1,'10 Apr 18 Review'!$A$2:$A$1000,1,1)
and then again as a second conditional formatting rule Cell Value not equal to.
The conditional format is set to fill the cell green if the course title exists on the datasheet entitled 10 Apr 18 Review and red if it does not.
Sometimes this works, sometimes it doesn't, by which I mean if I change the text in the 10 Apr 18 Review course title to test this, sometimes it highlights the corresponding course on the new worksheet red, sometimes it clears the highlighting i.e. leaving the cell white. Sometimes when I paste this into subsequent reviews (amending at which worksheet it looks to see what's changed accordingly) it reverses the formatting or won't see the courses in the worksheet I've pointed it to. To try and work out what is going on I did a conditional format based on whether said course existed on both sheets by simply comparing individual cells and this showed that the cells were the same (i.e. conditional formatting Cell Value equal to =10 Apr 18 Review!$A$2), so its not that the cells contain different information (they're both formatted as text).
Also, on the 10 Apr 18 Review worksheet, where this works (99%), the colour formatting works on all but the first course in the list. This will go green to reflect that it appeared in the previous catalogue download, but when I alter the old catalogue to test the conditional formatting it fills white; however, all subsequent courses go red when the previous catalogue download is changed as a test.
Can anyone see why the VLOOKUP formula above is providing inconsistent returns please, as Im beat.
Each month I am required to compare a course catalogue, identifying any new courses that have been added and any that have been deleted. The worksheet is exported from a SharePoint site into Excel where it is sorted alphabetically by course title. The following formula is then applied in the conditional formatting argument field as a Cell Value, equal to:
=VLOOKUP(A1,'10 Apr 18 Review'!$A$2:$A$1000,1,1)
and then again as a second conditional formatting rule Cell Value not equal to.
The conditional format is set to fill the cell green if the course title exists on the datasheet entitled 10 Apr 18 Review and red if it does not.
Sometimes this works, sometimes it doesn't, by which I mean if I change the text in the 10 Apr 18 Review course title to test this, sometimes it highlights the corresponding course on the new worksheet red, sometimes it clears the highlighting i.e. leaving the cell white. Sometimes when I paste this into subsequent reviews (amending at which worksheet it looks to see what's changed accordingly) it reverses the formatting or won't see the courses in the worksheet I've pointed it to. To try and work out what is going on I did a conditional format based on whether said course existed on both sheets by simply comparing individual cells and this showed that the cells were the same (i.e. conditional formatting Cell Value equal to =10 Apr 18 Review!$A$2), so its not that the cells contain different information (they're both formatted as text).
Also, on the 10 Apr 18 Review worksheet, where this works (99%), the colour formatting works on all but the first course in the list. This will go green to reflect that it appeared in the previous catalogue download, but when I alter the old catalogue to test the conditional formatting it fills white; however, all subsequent courses go red when the previous catalogue download is changed as a test.
Can anyone see why the VLOOKUP formula above is providing inconsistent returns please, as Im beat.
