Forum Discussion
Use colour to enter a logical value
Hi. I need to put a value into a cell to show we have a repeat caller. I've used the conditional formatting for duplicate numbers (the last part only, so not an exact science), so these have been coloured Light red fill with Dark Red Text. I want now to create a column which contains yes or no, depending on whether this is a repeat call, so a simple True/False would do.
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.
12 Replies
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.
Riny_van_Eekelen Hi, I've just pulled in data from the last month and I realise that some where there are blank cells it is pulling that in as a repeat call. At least I think that is what is happening. The data isn't being pulled through to the pivot table at despite being refreshed.
Jane_at_Stamma Can't really help without seeing the file.
Riny_van_Eekelen give me a while whilst I work out how to structure my table and work out what you did re the formatting...
Jane_at_Stamma Added a less structured, more old-style, for your reference. Might be easier to understand what I did. See attached.
