OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

sum a column EXCEPT a colored cell?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
coolawacs
Newbie
Newbie


Joined: 30 Jun 2003
Posts: 2

PostPosted: Mon Jun 30, 2003 8:02 pm    Post subject: sum a column EXCEPT a colored cell? Reply with quote

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
View user's profile Send private message Send e-mail
uros
Super User
Super User


Joined: 22 May 2003
Posts: 601
Location: Slovenia

PostPosted: Tue Jul 01, 2003 5:01 am    Post subject: Reply with quote

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
View user's profile Send private message
coolawacs
Newbie
Newbie


Joined: 30 Jun 2003
Posts: 2

PostPosted: Wed Jul 02, 2003 8:53 am    Post subject: Thanks, uros Reply with quote

Thanks,

Uros, I'll give that a try -- and reply how it works!

Al
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group