#### validation for a sum that must = 100%

```In Excell 2002 I have a worksheet where the user can
choose what percent of the time three different procedures
will be followed.  The total of the three choices MUST
equal 100.

Is there a way to get the data validation (or some other
non-VBA method) to tell the user his math doesn't add up
to, or goes over, 100?

i.e., using cells 1A, 1B, and 1C as entry cells, cell 1D
must = 100 (using the formula: "=1A+1B+1C" in cell 1D)

Thanks,
JR
```
 0
reithj (2)
8/19/2003 3:41:23 PM
excel.misc 78881 articles. 5 followers.

1 Replies
709 Views

Similar Articles

[PageSpeed] 55

```JR,

I would use conditional formatting.

Select D1.
Goto menu Format>Conditional Formatting
Set the test to 'Not equal to', the value to 100% or 1
Click Format, select the patter tab, and select a nice colour to highlight
it

--

HTH

Bob Phillips

"JR" <reithj@aol.com> wrote in message
news:082b01c36668\$583b7770\$a001280a@phx.gbl...
> In Excell 2002 I have a worksheet where the user can
> choose what percent of the time three different procedures
> will be followed.  The total of the three choices MUST
> equal 100.
>
> Is there a way to get the data validation (or some other
> non-VBA method) to tell the user his math doesn't add up
> to, or goes over, 100?
>
> i.e., using cells 1A, 1B, and 1C as entry cells, cell 1D
> must = 100 (using the formula: "=1A+1B+1C" in cell 1D)
>
> Thanks,
> JR

```
 0
bob.phillips (411)
8/19/2003 4:07:09 PM
 Reply:

Similar Artilces:

cumulative sum on different worksheets
i am working on a workbook with grades, it has 4 worksheets. i need to make a cumulative on each worksheet, from the totals, on the worksheets. thanks in advance for any help i can get. hi use something like =SUM('sheet1:sheet10'!A1) -- Regards Frank Kabel Frankfurt, Germany "deliliah" <deliliah@discussions.microsoft.com> schrieb im Newsbeitrag news:7A055DF3-8B95-4DDE-80C4-0F41754B63E8@microsoft.com... > i am working on a workbook with grades, it has 4 worksheets. i need to make a > cumulative on each worksheet, from the totals, on the worksheets. thanks in &...

Xml Schema: won't validate
Can someone help me with this? I keep double checking my schema layout and it looks like my implementation is correct... yet it must not be. I have the following schema which is invalid (Says: C:\Work\WIMAR XML\WIMARKeyedText.xsd(7): Type 'ImageHeaderType' is not declared. An error occurred at C:\Work\WIMAR XML\WIMARKeyedTexttest.xsd, (12, 6). ): <?xml version="1.0" encoding="utf-8" ?> <xs:schema targetNamespace="project" elementFormDefault="qualified" xmlns:mstns="http://tempuri.org/XMLSchema.xsd" xmlns:xs="http://w...

Query Question
Please see the sql code below. What I am after is Sum of [RiskCount]. But due to the join, I get double counting and consequently wrong SUM. Is this an inherent limitaion of Sql? Can be it solved without breaking up the sql into two parts? Thanks for reading this post. CREATE TABLE #Location ( LocationID INT, RiskCount INT) CREATE TABLE #Coverage ( LocationID INT, Peril VARCHAR(10), Coverage VARCHAR(10), TSI Numeric(25,6)) INSERT INTO #Location VALUES (1,1) INSERT INTO #Location VALUES (2,1) INSERT INTO #Location VALUES (3,1) INSERT...

Not working {={sum(if(A1:B3=C1:D3,1,0))} sometimes
I have values 11,12,13,14,15,16 in A1:B3 and in C1:D3 I have 1,2,14,3,4,5. That is, instead of matching 14 in A2 with C2, I put it in D2. Now if I use the above formula, I get a zero, also when I check for <> I get 6.. However, If I put it back to C2 I get 1 & <>=5. Why this irregularity? =SUM(COUNTIF(A1:B3,TRANSPOSE(C1:D3))) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "dindigul" <padhye.m@gmail.com> wrote in message news:urgkn8mlHHA.4960@TK2MSFTNGP02.phx.gbl... >I have values 11,12,13,14,15,16 in A1:B3 and in C1:D3 I have 1...

Data Validation #14
I am attempting to limit the input of a cell to between zero and on hundred percent. The cell is formatted for percent. I have tried th validation using both "custom" and "decimal" and in both cases after enter an invalid entry and "retry" the cell is reformatted so that if enter "20" it appears as "2000%" instead of "20%". Thanks for you help. Happy Holidays -- Message posted from http://www.ExcelForum.com The cell isn't reformatted. But unless you use an event macro, I don't think there's a way to bypass this d...

summing on subform and form
this seemed so easy when I first started, but everything I tried has not worked. I have a database with a form: form1 and a subform: subform1 the subforms are connected to the form by the SchoolID each school form will have a subform for each month. the subforms contain many numbers and calculations which seem for the moment to be working. I have a total field on each subform which I will need to sum and add to the parent form as a grand total. since I cannot SUM calculated fields I figured I would just pass the value into another field on the subform that I could then SUM. Curren...

Pivot Table
Good afternoon everyone. I have perused around a bit to see if anyone has asked this question yet and can't seem to find it or a reply. I have zero values in a large spreadsheet (the spreadsheet is a result of an Oracle query run on a regular basis). Without modifying the spreadsheet (to add columns), I would like to use a Pivot Table to subtotal (count and sum) all of the non-zero items for a given 'field'. I could accomplish this if one of the standard subtotals (in Field Settings...) was COUNTIF, but alas it isn't an option and I have not been successful in inserti...

Validation and entering data
Hi, I've got two spreadsheets. One has got data on, and one is blank but with validation on (from the data -> validation menu). I need to get the data onto the sheet with the validation and got through the validation process. If you paste or use vb commands such as range or cell, the validation is bypassed. Is there a way of simulating the act of typing in the data into the spreadsheet? Unfortunately I don't have control from either the source sheet, or the destination, but I can set up a routine to transfer the data. Thank you Ian ...

sum of a field filtered by date
HII have a table with commitments of customers on how much they will pay on what date. The fields are customer id, date and amount.Need to make a query to list out customer id, total payable till current date. I tried using sum function in total row in design grid, but it gives me some wierd result. other functions like avg, max and min work fine.also where can i specify that records with future dates are not to be included in the total?Thanks for any helpRamesh ...

Drop down / validation / lookup into the same cell
Hi I am using 2007 and would like to drop down from a list in a cell and have a lookup in the same cell eg. I have a list of employees and their numbers = emp_list and emp_number emp_list emp_number Joe 1 Jane 2 Bob 3 I want users to be able to select employees from a list then the model will look up the employee number IN THE SAME CELL. Now, I know how to get a drop down list of employees using date validation = emp_list. And I know how to lookup up the emp_number in a seperate cell using INDEX(MATCH()) or VLOOKUP. But I have ma...

Validation for Numeric values
I want to add validation to a cell as follows: Cell is formatted as Accounting, 2 decimal places Cell can have either whole nunmbers, or decimal entered into it However, some staff are also entering text So, what I want to do is, add validation to the cell that will accept a numeric value, with or without decimals, and prohibit the entry of anything else. Can it be done? TIA Duncs Select the cell; Use Data | Data Validation; specify Allow Decimal Optionally, open the other two tabs on the Data Validation dialog to add messages for the fools who type text. Typing text will now cause the P...

The user ID associated with the current record is not valid
Hi: I just install CRM 4.0 in new server running Server 2003 and SQL2005. After restore <organization>_MSCRM and MSCRM_CONFIG databases from old server I received this error: The user ID associated with the current record is not valid Any help to resolved this issue will be appreciated, -- Jose Valentin Rodriguez MCP-GP MCBMSS-GP You probably need to recreate the Special CRM groups in the SQL servers security to map to the restored database security. Look at the security on the database an make sure thoese groups exist in the global security of the SQL server. -- Gary Herbstman...

Help
I am running an Exchange 2000 Server and one of thje internal users has sent an email to internal and external recipients. The To address was an email and the CC address was a mixed internal and external distribution group. We also run MCaffee GroupShield for Exchange AV Software. The out queue has hundreds of emails and the internal users mailboxes are filling up PLEASE HELP!! First stage how can I remove this mail so it isn't sent anymore and second whats caused it and how do I prevent it! ...

sum of characters
What is the formula for finding the total number of letters in a range of cells? Example - How many x's and how many t,s in cels A1:G1. For the number of x's... =COUNTIF(A1:G1,"x") For the number of x's and t's... =SUMPRODUCT(--(ISNUMBER(MATCH(A1:G1,{"x","t"},0)))) Hope this helps! In article <1DE605A0-6B92-4D76-A477-609FFFAA2118@microsoft.com>, ddwchs <ddwchs@discussions.microsoft.com> wrote: > What is the formula for finding the total number of letters in a range of > cells? Example - How many x's and how many t,s ...

Sum with conditions #2
Hi, I've read a lot of posts in this group about this issue, but I can't find a solution. I am using this array formula: {=SUMA(((ConsultaPosiciones!E2:E100)=B1)*(ConsultaPosiciones!J2:J100); 0)} But I get "#VALUE!# (translated from spanish). I found that the problem is with som empty cells in column "J", when I set the values of those cells the function works. The problem is that I cannot set those values due the huge amount of data. Is ther any way to ignore those empty cells? Thanks in advance. I don't have SUMA in Excel 2003. I've always done this...

XSD Validation Question
To you XML validation experts: I have a schema that validates an XML file, but there are a couple of other types of validation I'd like to do on it. They are basically string fields that must be validated against a set of possible codes, but there could be tens of thousands of possible codes. Naturally, these codes are in a table in SQL (actually there are a few code types, but the problem is the same for each). In the past I have always done this as an additional code step AFTER schema validation, but I am being asked whether it could be "plugged into" the schema validation...

Validation Lists
If I have a column with a drop-down list, created from data validation can I pick more than one value in a single cell. For instance if the source of the list is Apple, Orange, Grape, Lemon, Lime In A1 I choose Orange, in A2 I choose Apple, I need to pick both Lemon and Lime in A3. How can I do this? One of the dropdown choices would have to be lemon and lime "Marsh" <Marsh@discussions.microsoft.com> wrote in message news:CEA088E3-FA45-44DC-BEBA-A3E0B366CFB1@microsoft.com... > If I have a column with a drop-down list, created from data validation can >...

Data validation on a custom format
Hi again everyone.... I am currently using the following custom format: [h]:mm How can I use data validation to prevent invalid data from being entered into a cell of that format?? For my purposes, I only want data which consists of 1 or more digits in the "hours" field, folowed by one colon, followed by 1 to 2 digits in the minutes field. Do I need to enter some sort of formula into the data validation field?? thank you! There's an option in the Data|Validation|Settings tab that you can set for Time. It sounds like you ...

help with restricting paste in cells with data validation
I have a range (column infact) in which i would like users to only select options from the drop-down... i added the following code that prevent paste into cell with data validation from the same excel... Private Sub Worksheet_Change(ByVal Target As Range) 'Does the validation range still have validation? If HasValidation(Range("ValidationRange")) Then Exit Sub Else Application.Undo MsgBox "Your last operation was canceled." & _ "It would have deleted data validation rules.", vbCritical End If En...

"Must" fill in cell
How do I make a field so that those completing it MUST fill it in? --- Message posted from http://www.ExcelForum.com/ Use Validation: Data > Validation. In the first tab choose "Custom" then in the Formul type this: =A1<>"" then uncheck "Ignore blank" If you want a warning sign, then click on last tab "Error Alert" an type in the title and message you want. Click Okay. Serenity99 wrote: > *How do I make a field so that those completing it MUST fill it in? -- Message posted from http://www.ExcelForum.com shades wrote: > *Use V...

Hlookup and Sum
Is it possible to combine a hlookup function with a sum function i.e. to look up a name in a table and return the sum of the first n cells beneath it. Dave, Sample testA testB testA testA testB 1 2 1 2 2 400 300 350 450 500 If I wanted to sum all of the values with testA in row 1 =SUMPRODUCT((A1:E1="testA")*(A3:E3)) returns 1200 If I wanted to sum all with testA in row 1 and 1 in row 2 =SUMPRODUCT((A1:E1="testA")*(A2:E2=1)*(A3:E3)) returns 750 logic returns True or False (1 or 0) for logical statements and numbers for ranges for example (A1:E1="testA") retu...

Auto opening of Validation list
I have a simple data validation rule applied to a cell which has the validation set as List and references approx 50 cells. What I would like to occur is that, when the sheet is selected, the drop-down list is revealed with the mouse hovering over the down arrow of the cell. Although there's no problem selecting the cell when the sheet is selected, I cannot get a macro to record the selecting of the cell dropdown list and cannot find a way to do it. It may not be possible for this to happen at all, but I would appreciate anyone's assistance if such a procedure can be done. Rob Hi ...

Sending to 100+ recipients
Hi I'm using Outlook 2003 on WinXP Home. I don't use exchange just personal folders. I have a genuine need to send a newsletter to a growing team and the list will soon exceed 100 people. Is there a limit set to the size of a distribution list either by Outlook, ISPs (in general) or by the actual email protocol? Am I going to have problems later? As a related question, I'd like to be able to have Outlook send the emails individually rather than as a single email with multiple recipients. Is this possible? I guess it would be with VBA somehow, something I might have a go at. ...

sum of columns
How do you make column the entire column c equal the entire column a + the enitre column B for each corresponding row? ie. c1 = a1 + b1, c2 = a2 + b2, c3 = a3..... and so on so forth. I've figured this out on an individual basis but I'd like to apply this to the entire colum, with out having to code each cell individually. Hi See http://computing.fandm.edu/training/excel/autofill.html on how to fill the C1 formula down. HTH. Best wishes Harald "chusome" <matt.tremblay@gmail.com> skrev i melding news:1118241671.104209.230800@f14g2000cwb.googlegroups.com... > How...

list sums from multiple worksheets
how do i list individual sums from multiple worksheets to seperate cell in a master worksheet? all individual worksheets are identical i structure -- Message posted from http://www.ExcelForum.com Unless I'm misunderstanding your purpose I believe you simply need t include the specific spreadsheet name in the formula for the cell. For example to display the sum of C1:C50 of Sheet 1 in cell B2 of Shee 2, use in that cell the formuls: =sum(Sheet1!C1:C50) This also works for data contained within other workbooks -- Message posted from http://www.ExcelForum.com example sheet2 cell a20...