Forum Discussion
Use colour to enter a logical value
Jane_at_Stamma Transform your table to a structured Excel table and use a formula like in the "repeat" column to count the number of occurrences of the current caller ID up to the current row. If 1, then this is first time caller. Anything higher than 1, then it's a repeat caller.
By the way, I changed the conditional formatting rules a bit. See attached.
Jane_at_Stamma Can't really help without seeing the file.
Riny_van_Eekelen here you go
Jane_at_Stamma OK, I amended the "Repeat" formula in the master table and created a new pivot table in the "Repeat Callers" sheet. Records with a "cli" will not be counted as "Yes" and the pivot table pulls in the July data as intended. You'll notice that my table is structured differently from yours. Can't really tell why you decide to do a count of the Count column where you could count the number of CallID's (in the Values field) for all records where the "repeat" code is "Yes" (in the Filter field).
See if this is what you expect to see.
thank you Riny_van_Eekelen for your time! I updated the pivot table - lord I find them really hard - and have noticed that now I have a number of calls labelled as repeat=yes, (3) in both June and July, which aren't coloured pink, nor can I find when they are repeated. all the other months/years look fine
Jane_at_Stamma Not sure what you did but you ended up with three conditional formatting rules that did not cover the entire cli column. Get rid all the rules and create a new one with an "Applies to" range like $E$2:$E$3524 . When you now add data at the bottom of the table the CF rule should expand automatically.