I'm trying to work some trickery in excel where each cell in a row is shaded based on how many have been completed:
So the idea is that as more of the columns in the row are ticked the whole row (or at least the task column cells) go from red towards green.
I'm trying to use the three-colour-scale Colour Scales conditional formatting in Excel 2013 but I'm not having much luck. I started by creating a helper column that returned the amount of completed tasks and setting the formatting to look at that, but apparently this function doesn't accept indirect references.
I thought I would be able to use the Formula type:
But neither of these formula work:
=MIN(COUNTA($B2:$G2)) =(COUNTA($B2:$G2))/2 =MAX(COUNTA($B2:$G2))
=(COUNTA($B2:$G2)=0 =(COUNTA($B2:$G2)=3 =(COUNTA($B2:$G2)=6
It's pretty clear to me that I'm misunderstanding the fundamental use of the Formula type, but I'm having a huge amount of difficulty finding any clear information on it via our old friend Google.
So my question is; what is the correct use of the Formula type, where am I going wrong and how can achieve the effect mentioned above?
So I've managed to solve the specific task I was attempting:
To get the ticks I used the font Marlett and set the cell value to a.
Instead of entering the letter a (this is part of a small VBA function I wrote that ticks and unticks cells when you double click on them) I added a small function to the script that added a number to the cell instead, then set the custom number format to "a".
The number references the column number of the active cell minus 1 giving me the correct number for the cell so that I can then set the conditional formatting on numbers with 1 at the bottom and 6 at the top, giving me a nice growing success gradient as more cells are ticked!
Sadly this still doesn't really answer my question, as I still don't understand the proper use of the Formula type in the conditional formatting settings.