How set totals of various cells with per colour fill value?

I have a sheet containing values as follows.


                     April  May  June
company 1      43
company 2                        5
company 3              17


The cell values are also coured to indice a status.
Somewhere beside the table I want to mention the totals of the cells,
per status (so per colour).
How to perform the latter?



Thank you.

Bart
Excell 2003

0
5/4/2006 4:19:15 AM
excel 39879 articles. 2 followers. Follow

4 Replies
218 Views

Similar Articles

[PageSpeed] 37

See http://www.xldynamic.com/source/xld.ColourCounter.html for a working
solution

-- 
 HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"AA Arens" <bartvandongen@gmail.com> wrote in message
news:1146716355.504026.234920@i39g2000cwa.googlegroups.com...
> I have a sheet containing values as follows.
>
>
>                      April  May  June
> company 1      43
> company 2                        5
> company 3              17
>
>
> The cell values are also coured to indice a status.
> Somewhere beside the table I want to mention the totals of the cells,
> per status (so per colour).
> How to perform the latter?
>
>
>
> Thank you.
>
> Bart
> Excell 2003
>


0
5/4/2006 8:51:18 AM
Bob, where to set the script in VB, I cannot find the macro editor in
Version 12 of Excel.

Bart

0
5/6/2006 5:14:27 AM
Bob, where to set the script in VB, I cannot find the macro editor in
Version 12 of Excel.

Bart

0
5/6/2006 5:15:15 AM
Goto the VBIDE (Alt-F11), add a new code module (Insert>Module)< and add it
there.

-- 
 HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"AA Arens" <bartvandongen@gmail.com> wrote in message
news:1146892515.087588.64830@i40g2000cwc.googlegroups.com...
> Bob, where to set the script in VB, I cannot find the macro editor in
> Version 12 of Excel.
>
> Bart
>


0
5/10/2006 12:53:57 PM
Reply:

Similar Artilces:

Reading a cell based on a vairable
Hello, I am trying to do something really simple, but getting a bit stuck. I have sheet1 with a unique list of names, and sheet2 with a list of the same names (not unique) and a number next to then. I would like to go down the list in sheet1, and put a total next to the name, from all the entries in sheet2 which match. The first thing I want to do, is read the contents of cell A1 into a variable. I need to do it in a loop though, so I need to reference the cell via this. How can I do something like: variable = cell( a, rowNum) (where "variable" and "rowNum" are variabl...

Can a cell be turned on or off?
A1=B1*C1+D1 A2=B2*C2+D2 A3=B3*C3+D3 A4=B4*B4+B4 I would like to be able the have the total in any one of the A cells to add up only if the cell is click on. In other words, can a cell be turned off or on as needed? -- Joe Right click sheet tab>view code>insert this Private Sub Worksheet_BeforeDoubleClick _ (ByVal Target As Range, Cancel As Boolean) If Target.Column <> 1 Then Exit Sub 'B1*C1+D1 tr = Target.Row Target.Value = Cells(tr, 2) * _ (Cells(tr, 3) + Cells(tr, 4)) End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@g...

Total PC Defender
Would anyone know of a surefire method of removing Total PC Defender & Trojan.Injector.GT ? I think I've been able to remove Total PC Defender (partially), but not the Trojan. AVG free still isn't allowed to update, Malewarebytes wont run, and my administrative rights have changed... wont allow me to install Windows Defender. I'm hoping someone has written a stand alone DOS utility or removal tool... would be nice. Kindof pulling my hair out over here! Thanks for any help. Richard in VA +++++++++++ Richard In Va. wrote: > Would anyone know o...

MS Excel Cells
can i split a single cell in excel diagonally and put type in each side ? Short answer is no. But you can fake a lot of things http://groups.google.com/groups?threadm=jemcgimpsey-32EEF4.06432707022004@msnews.microsoft.com --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Ddman" <Ddman@discussions.microsoft.com> wrote in message news:B6E85364-9...

Posting 60 messages per second to CView locks up UI (animation continues...)
Hi, As recommended by Jerry and Scott in this newsgroups, I experimented with CreateTimerQueueTimer and PostMessage(WM_APP_...) to drive animations in a CView derived window. I have 2 types of animations: GDI based and Direct3D based. I noticed that Direct3D based animations were really smooth when I ran them at a frequency which was close to the adapter's refresh rate: 60hz. I was quite pleased to see the smoothness of the animations and a very low CPU usage (alternating between 3-4%). Unfortunately the app's UI (e.g. closing/sizing/moving the frame, menus, etc.)...

Setting recordsource property in VBA Access 2007
I'm trying to create a report in Access 2007. I cannot figure out how to set the recordsource propery in code. I either get #Name? if the property is not set at design or I get the original dataset base on the value of recordsource at designed, not the value that I set in the report_Open event. Does anyone know what I need to do to be able to set the recordsource property in code? Thanks, Kevin KMcHenry wrote: >I'm trying to create a report in Access 2007. I cannot figure out how to set >the recordsource propery in code. > >I either get #Name?...

Set up form fields to that Tab can be used to move from one to ano
Hi! I'm creating a form in Excel 2003 with a variety of text boxes, check boxes, and drop-down fields, all created using the Control Toolbox. I'm having trouble figuring out how to set these form elements up so that the user can press the Tab key to move from one field to the next. The Properties dialog box for the text boxes has something called TabKeyBehavior, but when I set it to True, all it does is allow you to insert a tab space within the text string in the field. I want to use the Tab Key to move the cursor to the next field in the form. I have been unable to find...

Setting global variable with result from SQL statement
I am trying to obtain the status of a sales order, and store that status in a global variable. There is only ever 1 record that is returned from my SQL statement which obtains the status, but how do I put the result into the global variable ? I can amend the SQL to put the result in a table, but that just pretty much puts me in the same position. The only thing I can think of, is to open a form with the results of my SQL, and set the variable to the value of the linked text box on the form, then close the form, but this would be very messy, and I'm sure its not the correct way to...

Using vlookup against blank cell
I am using vlookup to return values (lets say values A-I) based on other values (Values 1-10) in column A. Sometimes there is no value in column A and I'd like vlookup to return a value "blank cell" or whatever I define. Right now blank cells return N/A. I am guessing there is something simply I can do there. Thanks in advance bmac One way: =IF(ISNA(MATCH(J1,A:A,FALSE)),"blank cell",VLOOKUP(J1,A:B,2,FALSE)) In article <#2YO5vFFIHA.4752@TK2MSFTNGP04.phx.gbl>, "bam" <bartmacl@yahoo.com> wrote: > I am using vlookup to return values (...

Comparing two sets of data
I need to compare sets of data in two different spreadsheets in two different columns labeled the same, Grant Number. We are trying to track if both sets have the same grant numbers or if there are duplicates. Did you adjust the ranges from column Z to whatever column held your data? (and did you remove the extra trailing close parenthesis?) Another formula that I use is this: I insert a helper column to the right of the Key data. Then I use a formula like this in Grant1 (assumes data is in column B of both sheets). =IF(ISNUMBER(MATCH(B2,Grant2!$B$2:$B$9999,0)),"Match",&quo...

Collect value
I have 4 cells: H8 I8 J8 K8 If change H8, I8 J8 K8 cells have variable value. I want to change values of H8 from 10 to 150 and collect all 4 cells value in columns AF AG AH AI. Please help me with same VBA code. Thanks. This routine will do the data entry for you and capture the results. To put the code into your workbook, open it and press [Alt]+[F11] to open the VB Editor. Use Insert --> Module in it to open a new code module, then copy and paste the code into it. Select the proper worksheet and then run the Macro. Sub FillAndCapture() Dim homeCell As Range Dim ...

Pivot Table (blank) Cells
Can I remove the '(blank)' entry that appears in pivot table cells that have no contents? Thanks Select one of the cells with (blank) in it. Hit the space bar and hit enter. This is really replacing the (blank) with a single space character. koala824 at Comcast wrote: > > Can I remove the '(blank)' entry that appears in pivot table cells that have > no contents? Thanks -- Dave Peterson Pretty slick. Works great, even if I cannot follow the programming logic of it all. My mindset never considered actually editing the contents of a pivot ...

How can I add more than 2 text box values?
I am working on an accounting database with differening exchange rates. I have been attempting to create one text box with a balance value after using the correct exchange rate. For instance I have Box1 = 2345.00 pesos and Box2 =2,100.00 (exchange rate) and I have Box3 with a dollar ammount. How do I divide Box1 by Box2 and add Box3 for a Balance, that I can use in a report or in an other table. I've tried using =sum([Box1]/[Box2]+[Box3]) on the form side, and on the query side but it gives me an error. and I still don't know how to label it to be used by a report or to be used wit...

Global settings size limitation on Exchange Server 2003
I set send and received mails as 1MB max. from global settings on Exchnage 2003 server. Then some users need more over 1MB sized mail. I set user settings of this users to 5MB. But they cannot send or receive any mail over 1MB from outside of organisation. I searched and found that user settings cannot override global settings for internet mails. How can I increase limitations for special users. By the way, I try to ommit 1MB limit for all as temporarily. I changed global settings as "no limit" but still I cannot send or receive any mail over 1MB. How can I solve these problem...

Clearing the Contents of Merged Cells
How would I implement VBA to clear the contents of a range that contains merged cells? The following code results in a runtime error '1004' : "Cannot change part of a merged cell." ***************************************************** If Union(ActiveCell, Range("ProbArea")).Address = Range("ProbArea").Address Then Ans = MsgBox("Do you want to CLEAR the contents of CURRENTLY SELECTED CELL'S ROW?", vbYesNo) If Ans = vbNo Then Exit Sub End If If Ans = vbYes Then Application....

Fill drop list
i want the user to be able to open a document like word or excel by selecting it from a combo drop list. how can i fill the drop list with the files of a particular folder? On Mon, 7 Dec 2009 09:00:01 -0800, Pastor Del wrote: > i want the user to be able to open a document like word or excel by selecting > it from a combo drop list. how can i fill the drop list with the files of a > particular folder? Copy and Paste the below code into a Module. Public Sub GetlistFiles() ' fill a List Box with the names of files found in folder Dim MyName As String Dim FS As...

Setting of @@FETCH_STATUS
I have a snippet of code from a trigger: DECLARE @Saved_ID int SET @Saved_ID = (SELECT max(ID) FROM Table WHERE Name = @UserName) IF @@FETCH_STATUS = 0 AND @Saved_ID IS NOT NULL UPDATE ELSE INSERT Turns out my code ALWAYS runs the INSERT. If I add a temporary variable: DECLARE @Saved_ID int DECLARE @SavedStatus int SET @Saved_ID = (SELECT max(ID) FROM Table WHERE Name = @UserName) SET @SaveStatus = @@FETCH_STATUS IF @SavedStatus = 0 AND @Saved_ID IS NOT NULL This code works.... The only thing I can figure is the "IS NOT NULL&qu...

fill list box with file names
how to fill list box with file names sorted by date - newest first. tia, mcnewsxp On Tue, 20 Apr 2010 09:04:46 -0700 (PDT), mcnews <mcourter@mindspring.com> wrote: >how to fill list box with file names sorted by date - newest first. >tia, >mcnewsxp Look at the VBA help for the Dir() function, and use a loop to fill a Table. Use that table (or a query based on the table) as the Row Source for the listbox. -- John W. Vinson [MVP] 1. What is the make of the table holding these names? 2. What is the name of the date field? 3. What is the name of ...

How to set sales target for each salesperson?
Hi all, 1. How can I set sales target for each salesperson for a specific fiscal year? Can I use the "salespeople with quota" section? My machine has disabled it due to wrong fiscal year date setting. 2. Can I change the fiscal year settings to enable the section again? 3. Can this section set different sales targets for different sales people? Please advice me. Tnx in advance ...

Setting archive rules on Multiple folders??
Hi there, I have about 100 or so folders and sub-folders etc in my PST file... Into which I organise all the emails I handle. I want to set up archiving rules on them. As far as I can tell the only two ways I can do this is 1) Set the DEFAULT rule and apply it to ALL FOLDERS in one swoop. 2) Set the Archive setting on each individual folder. Option 1 is not much use to me because I need different settings on various folders... many I don't even want archived. Option 2 is going to take a really really long time... because I am not able to select more than one folder at a time nor can I...

I have opened an Excel file and I can't edit any cell
Please post any questions in the body of the message this is considered rude http://www.cpearson.com/excel/newposte.htm Are the sheets protected? -- Regards, Peo Sjoblom Tools > Protection > unprotect worksheet But you will need a password Cheers peterm "Elise0" wrote: > ...

Use percentages instead of values
I have a series of numbers that show how a hard drives space is being used. The table shows the figures in GB values. How do I get a pie chart based on the table to show the % per section and not the GB value? In the data labels tab of the format series or chart options dialogs, you can select percentage instead of value for display in the labels of a pie chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Keith" <Keith@discussions.microsoft.com> wrote in message news:06...

Is there some kind of color reference table for values?
Hi, Im using a flexgrid that lets me set fillcolors etc... i saw some code that defined some colors but i wanted to know if there was a chart so i can figure out my own colour schemes.. the code was... Const conCream As Long = 13303807 Const conNavy As Long = 8404992 Const conPaleGreen As Long = 13434828 Const conLightBlue As Long = 16760445 thanks On Tue, 23 Oct 2007 21:49:15 +0100, "Craig Armitage" <craig@icarmitage.co.uk> wrote: >Hi, > >Im using a flexgrid that lets me set fillcolors etc... > >i saw some code that defined some colors but i wanted to...

alphabetical order within a cell
Hi I have lists of postcodes [zip codes] in cells - many codes together in the same cell - , but only the first two characters. For example: CT, TN, BN, RH, etc etc. There are a lot of them. Could someone tell me how to get Excel to order these alphabetically in each cell that contains them please? Note..this is not about a single code in its own cell but multiple codes in a single cell. Many thanks David Take the code below and put it into a code module. Choose the cell to sort contents on (one at a time) and run this code/macro. I've tried to break long lines so that it c...

during set up tells me that the ost file is not a offline folder #2
when I went to set up outlook when it attempts to create a default folderit tells me that the outlook.ost file is not an offline folder - when I click OK it closes outlook - so I am in a loop ...