| View previous topic :: View next topic |
| Author |
Message |
coolawacs
Newbie
Joined: 30 Jun 2003
Posts: 2
|
 Posted: Mon Jun 30, 2003 8:02 pm Post subject: sum a column EXCEPT a colored cell? |
 |
|
I have a large ss, I would like to (manualy) color selective rows, and then perform a sum operation on a given column -- except the cells that are of a particular color. I've been told by 5 people it can't be done, is this correct, is this not possible?
Al |
|
| Back to top |
|
 |
uros
Super User
Joined: 22 May 2003
Posts: 601
Location: Slovenia
|
| Posted: Tue Jul 01, 2003 5:01 am Post subject: |
|
|
I don't know any spreadsheet function to do that. It colud be done with macro like this.
It reads not only values but characters colour too and then decide to sum the value or not.
| Code: |
Sub test()
oSheets = ThisComponent.Sheets
oSheet = oSheets.getByIndex(0) ' data on the first sheet
sColumn = 3 ' in column D
sRow = 0 ' starting from second row
sSum = 0
Do
sRow = sRow + 1
oCell = oSheet.getCellByPosition(sColumn,sRow)
sColour = oCell.CharColor ' character colour
' sColour = oCell.CellBackColor ' background colour
sValue = oCell.Value
If sColour <> 16711680 Then ' Light red
sSum = sSum + sValue
End If
Loop Until sValue = 0 ' sum until first empty cell or value 0
oCell.Value = sSum ' write the result under your data
End Sub
|
I'm sure there are more elegant ways to do the same.
Uros |
|
| Back to top |
|
|
coolawacs
Newbie
Joined: 30 Jun 2003
Posts: 2
|
| Posted: Wed Jul 02, 2003 8:53 am Post subject: Thanks, uros |
|
|
Thanks,
Uros, I'll give that a try -- and reply how it works!
Al |
|
| Back to top |
|
|
|
