Always have cells add up to 100

's Computer Specifications

I have range of cells that are the same right now, and they add up to
100:

7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857

100

I would like to be able to change on of those values, and the others
adjust to still add up to 100. Any ideas?

's Computer Specifications

Right click sheet tab>view code>insert this>SAVE workbook

Private Sub Worksheet_Change(ByVal Target As Range)
Set myrng = Range("e2:e15")
Application.EnableEvents = False
If Not Intersect(Target, myrng) Is Nothing Then
x = 100 - Target
For Each c In myrng
If c.Address <> Target.Address Then c.Value = x / 13
Next
End If
Application.EnableEvents = True
End Sub

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Jeff" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I have range of cells that are the same right now, and they add up to
> 100:
>
> 7.142857
> 7.142857
> 7.142857
> 7.142857
> 7.142857
> 7.142857
> 7.142857
> 7.142857
> 7.142857
> 7.142857
> 7.142857
> 7.142857
> 7.142857
> 7.142857
>
> 100
>
> I would like to be able to change on of those values, and the others
> adjust to still add up to 100. Any ideas?
>

's Computer Specifications

OP did not ask for it.
--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Charles Chickering" <(E-Mail Removed)> wrote in
message news:7714C40C-65C0-4D25-BF48-(E-Mail Removed)...
> Don, why not use:
>
> f c.Address <> Target.Address Then c.Value = x / myrng.Cells.Count
>
> as opposed to a hard coded number?
> --
> Charles Chickering
>
> "A good example is twice the value of good advice."
>
>
> "Don Guillett" wrote:
>
>> Right click sheet tab>view code>insert this>SAVE workbook
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> Set myrng = Range("e2:e15")
>> Application.EnableEvents = False
>> If Not Intersect(Target, myrng) Is Nothing Then
>> x = 100 - Target
>> For Each c In myrng
>> If c.Address <> Target.Address Then c.Value = x / 13
>> Next
>> End If
>> Application.EnableEvents = True
>> End Sub
>>
>>
>> --
>> Don Guillett
>> SalesAid Software
>> (E-Mail Removed)
>> "Jeff" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> >I have range of cells that are the same right now, and they add up to
>> > 100:
>> >
>> > 7.142857
>> > 7.142857
>> > 7.142857
>> > 7.142857
>> > 7.142857
>> > 7.142857
>> > 7.142857
>> > 7.142857
>> > 7.142857
>> > 7.142857
>> > 7.142857
>> > 7.142857
>> > 7.142857
>> > 7.142857
>> >
>> > 100
>> >
>> > I would like to be able to change on of those values, and the others
>> > adjust to still add up to 100. Any ideas?
>> >
>>
>>
>>

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
That document contains too many cells. At this time, spreadsheets inGoogle Docs only support up to 400000 cells	Mark	Microsoft Excel Misc	0	19th Jan 2011 03:08 PM
copy an intial cells contents into the next series of blank cells ina column	freeriderxlt	Microsoft Excel Discussion	2	25th Aug 2009 07:47 AM
Excel: match two cells in one sheet to two cells in another and return a third cells value	Spence	Microsoft Excel Worksheet Functions	2	19th Jul 2005 04:19 AM
Skip cells with TAB/SHIFT+TAB but allow arrow keys/mouse selection of skipped cells	Wescotte	Microsoft Excel Programming	1	6th Jun 2005 07:00 PM
A calculation to count all cells ='D' where the offset cells in range have dates <= todays date	AlanN	Microsoft Excel Misc	2	29th Jan 2004 03:57 PM