Hi,
I would appreciate any thoughts and help on why my conditional formatting only seems to work sporadically. I've tried looking in the forums but as I am not exactly sure what the problem is I spent lots of time reading - which was interesting - but not getting anywhere fast!
I attach a workbook with a sample of the data. Two sheets, the main one, 'Personnel', with data that is pulled through to a 'Training Log' sheet (haven't finished that all yet).
On the Training Log sheet I want the name of the person (column A) to be conditionally formatted (simple shading) based on data in column M (enrolment code) of the Personnel sheet. So, the formula has to find the same name and then look along the row to check if there is an entry (sorry if this is like asking you to suck eggs!). If it is possible to indicate through conditional formatting based on the 5 enrolment codes which one (5 different shaded colours) that would be great...starting simply however, I have used the formula below just to see if there is an entry:
=NOT(ISBLANK(VLOOKUP(A3,Name_Data,9,FALSE)))
but it doesn't always work!
I am using the named range 'Name_Data' as I understand the formula won't work using the reference "Personnel!E2:Q12".
Please be aware the Personnel sheet normally contains several hundred entries. Maybe because of this, or because I cannot put them in ascending order, the formula above does not seem to work reliably.
Weirdly, it works fine on this smaller sample set of data.
Any help would be much appreciated. For example, I was going to try using MATCH and INDEX but am not quite sure how to start since this is not a normal cell formula, but a conditional formatting one.
Thanks very much for your thoughts!
Nomades
Bookmarks