I have a question regarding the conditional formatting. I have this 4 columns listing with continuous entries day by day by different people.
What I try to achieve is that for the specified criteria (within a date range and for particular name) the conditional formatting will highlight those entries that meet the criteria
Name Code Date Rate
John B TYT6678DDJ 10/12/2013 160.23
William H KLK998DDS 31/10/2013 120.36
Matt R HJKJH78DS 05/10/2014 201.23
For example if someone enters the “John B” again in row 100 and dated 31/01/2014 (date range set as after 1st January 2014 but before 31 October 2014) then the row 100 will be automatically highlighted in certain colour. To achieve this I might have a separate table for criteria setting purpose like this:
Name From Date To Date
John B 01/01/2014 31/10/2014
How can I set up the conditional formatting to achieve the above?
hard to answer questions that are "I might", but assuming you have a table of conditions as you show, I think I would use the multi-condition countifs function to determine if a table row entry is met. So if the countifs returns a value greater than zero, you know that row meets a condition in your table.
assume your table is in AB5:AD20 and column A has Name, column B has code, column C has Date, column D has Rate
for row 100
would be the conditional formatting formula. Of course this is what it would look like for row 100 but would be entered for all your rows and columns at one time and be written relative to the activecell when you did the entry. I am just giving an example for row 100 so it is hopefully more illustrative of the technique.
if you wanted a date range as a condition, then you would need to have a start date column and an end date column in your table. You would add a condition for the date in the row being >= start date and another condition for the row date being <= end date
If you want to to highlight duplicates then you would use a progressive range and not use a table
so say in row 5
as that is filled down to row 6 it becomes
because the row for the terminating range part is not fixed - it is variable.
So there are some ideas which will hopefully help you solve your problem.