how can i make an excel formula that omits shadded cells? it is for college football games?

Question

how can i make an excel formula that omits shadded cells? it is for college football games
I was given a spreadsheet for college football games and when the winner is selected, those people who got the game wrong get their picks shaded out, how can I make a total points colum that will omit shaded boxes? basically shaded boxes would equal 0 and unshaded would equal1????? please help!!!!

Answer 1

If you are using Excel-2007 or later you can use auto filter based on filled colours or formatting of cell.



if you are using 2003 or earlier version then i recommend you to Put any value instead colouring the cell and then apply autofilter and filterout the not required value.



or Download ASAP Utilities that has a feature that can Hide, Unhide or Delete the cells that matches to a particular selection.





(*.*)

Answer 2

As far as I know, there is no way to use shading in a formula.



I would shade the cell, and put a 1 or 0 in it. Then, write your formulas based on the 1 or 0 in the cell. If you want it to look really good, set the font color to the shading color, then you won't see the 1 or 0.

Answer 3

Do Alt-F11 and paste this short piece of code into a module:-



Function Xcolor(target As Range) As Integer

Xcolor = target.Interior.ColorIndex

End Function



Function Ycolor(target As Range) As Integer

Ycolor = target.Font.ColorIndex

End Function



Function Zcolor(target As Range) As Integer

Zcolor = target.Borders.ColorIndex

End Function



___________________________________________________



The function XCOLOR calculates the reference number of the fill color of a cell, so you can use it in formulas, for instance:-



IF(XCOLOR(A1)=-4142,1,0)

Answer 4

you would have to record a macro