Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 ============================ |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 ============================ |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.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. :-)) |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. :-)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i autofill a cell a certain colour based on it's value? | Excel Worksheet Functions | |||
Calculations based on cell value | Excel Worksheet Functions | |||
Calculations based on adjacent cell values | Excel Discussion (Misc queries) | |||
Colour Cell based on Content | Excel Worksheet Functions | |||
How do I set a colour to 4 cells based on the value of a cell | Excel Discussion (Misc queries) |