Calculations based on cell colour
 

With Excel 2007, is it possible to create a formula that will sum,
based on the shading or colour of a cell ? I'm looking for an easy
way to pick up items that have been billed and calculate the balance
outstanding without having to change the formulae all the time.

Regards
Fred

Calculations based on cell colour
 

On Mon, 3 Oct 2011 04:28:23 -0700 (PDT), Fred wrote:

With Excel 2007, is it possible to create a formula that will sum,
based on the shading or colour of a cell ? I'm looking for an easy
way to pick up items that have been billed and calculate the balance
outstanding without having to change the formulae all the time.

Regards
Fred

You could filter by color and then do subtotals. You could also automate this process in VBA.

But how is the cell being color'd? Is this something you do manually when you send out a bill? Or is this done by conditional formatting?

If the latter, you could use the same formula in your balance calculation as you do for conditional formatting.

Calculations based on cell colour
 

Unfortunately the data is in rows rather than columns, so the filter
is not easily applied.

The cells are, to quote the finance guy, "highlighted", looking at the
cells he appears to have filled using Format Cells. They all have
values and only get shaded once the bill has been sent out. What I was
hoping for was somthing like
=SUMIFS(A3:A15,CellColour="Blue") in the Billed to date column
and
=A2-(SUMIFS(A3:A15,CellColour="Blue") in the Remaining column,
where A2 contains the total amount to be billed.

Fred

Calculations based on cell colour
 

Unfortunately the data is in rows not columns, so filtering is
difficult

According to the finance guy, the cells are shaded when the bill goes
out, looking at the cell he's simply done a Format Cells and set the
Fill colour.

What I was hoping for was something like

=SUMIF(A3:A15,CellColour="Blue") In the Billed to Date
column
and
=A2-(SUMIF(A3:A15,CellColour="Blue") In the Remaining to be
billed column, where A" contains the total billable

Cells A3:A15 contain the bilable amount and all cells are filled, so
we can't test for 0/blank/empty cells and he currently has to go
through and change the formulae each period to pick up the correct
cells

Fred

Calculations based on cell colour
 

On Mon, 3 Oct 2011 09:26:16 -0700 (PDT), Fred wrote:

Unfortunately the data is in rows not columns, so filtering is
difficult

According to the finance guy, the cells are shaded when the bill goes
out, looking at the cell he's simply done a Format Cells and set the
Fill colour.

That seems prone to error, but WTFDIK.



What I was hoping for was something like

=SUMIF(A3:A15,CellColour="Blue") In the Billed to Date
column
and
=A2-(SUMIF(A3:A15,CellColour="Blue") In the Remaining to be
billed column, where A" contains the total billable

Cells A3:A15 contain the bilable amount and all cells are filled, so
we can't test for 0/blank/empty cells and he currently has to go
through and change the formulae each period to pick up the correct
cells

Seems like the data you are adding is in columns.

You can do this with a User Defined Function.


To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=SumBlue(A3:A15)

in some cell.

You may have to fiddle a bit to figure out which colorindex number; or color (or colors) your finance guy is using. And if he changes, the UDF will fail.

I would suggest, however, that he do something less complicated than manually reformatting the cell. It seems to me that entering the billing date in some cell in the same record would be of value; then you could test that date in an regular SUMIF formula; and he could also color the cell using conditional formatting; and even use different colors, via conditional formatting, to show information about the aging.

=======================
Option Explicit
Function SumBlue(rg As Range) As Double
Dim c As Range
Dim t As Double
For Each c In rg
If c.Interior.ColorIndex = 23 And IsNumeric(c.Value) Then
t = t + c.Value
End If
Next c
SumBlue = t
End Function
============================

Calculations based on cell colour
 

Sounds like that's just what I need, thanks for the pointer, i'll give
it a try tomorrow.

Regards
Fred

Calculations based on cell colour
 

Hi Ron
I think you have a Typo!!!That seems prone to error, but WTFDIK.
Change to this "WTHDIK. ..LOL

"Ron Rosenfeld" wrote in message
...
On Mon, 3 Oct 2011 09:26:16 -0700 (PDT), Fred wrote:

Unfortunately the data is in rows not columns, so filtering is
difficult

According to the finance guy, the cells are shaded when the bill goes
out, looking at the cell he's simply done a Format Cells and set the
Fill colour.

That seems prone to error, but WTFDIK.



What I was hoping for was something like

=SUMIF(A3:A15,CellColour="Blue") In the Billed to Date
column
and
=A2-(SUMIF(A3:A15,CellColour="Blue") In the Remaining to be
billed column, where A" contains the total billable

Cells A3:A15 contain the bilable amount and all cells are filled, so
we can't test for 0/blank/empty cells and he currently has to go
through and change the formulae each period to pick up the correct
cells

Seems like the data you are adding is in columns.

You can do this with a User Defined Function.


To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=SumBlue(A3:A15)

in some cell.

You may have to fiddle a bit to figure out which colorindex number; or color
(or colors) your finance guy is using. And if he changes, the UDF will fail.

I would suggest, however, that he do something less complicated than manually
reformatting the cell. It seems to me that entering the billing date in some
cell in the same record would be of value; then you could test that date in an
regular SUMIF formula; and he could also color the cell using conditional
formatting; and even use different colors, via conditional formatting, to show
information about the aging.

=======================
Option Explicit
Function SumBlue(rg As Range) As Double
Dim c As Range
Dim t As Double
For Each c In rg
If c.Interior.ColorIndex = 23 And IsNumeric(c.Value) Then
t = t + c.Value
End If
Next c
SumBlue = t
End Function
============================

Calculations based on cell colour
 

Fred expressed precisely :
Unfortunately the data is in rows not columns, so filtering is
difficult

According to the finance guy, the cells are shaded when the bill goes
out, looking at the cell he's simply done a Format Cells and set the
Fill colour.

What I was hoping for was something like

=SUMIF(A3:A15,CellColour="Blue") In the Billed to Date
column
and
=A2-(SUMIF(A3:A15,CellColour="Blue") In the Remaining to be
billed column, where A" contains the total billable

Cells A3:A15 contain the bilable amount and all cells are filled, so
we can't test for 0/blank/empty cells and he currently has to go
through and change the formulae each period to pick up the correct
cells

Fred

Why does the 'finance guy" not just put a value in the 'Remaining to be
billed' column so you can use that to exclude rows you don't want
summed?

Example:
=SUMIF(A3:A15,ToBeBilled="")

...where ToBeBilled is a defined name having local (worksheet) scope,
which refs the 'remaining to be billed' column.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Calculations based on cell colour
 

On Mon, 3 Oct 2011 15:22:42 -0400, "Cimjet" wrote:

Hi Ron
I think you have a Typo!!!That seems prone to error, but WTFDIK.
Change to this "WTHDIK. ..LOL

I'm just in a lower class. :-))

Calculations based on cell colour
 

No, just more human.
"Ron Rosenfeld" wrote in message
...
On Mon, 3 Oct 2011 15:22:42 -0400, "Cimjet" wrote:

Hi Ron
I think you have a Typo!!!That seems prone to error, but WTFDIK.
Change to this "WTHDIK. ..LOL

I'm just in a lower class. :-))