Excel 2000 - using sliders & setting maximum value for a range 1

[Chats]

Hi,

I am trying to set up a spreasheet where there are five values (range B2:B5) the values of which can be manipulated using some kind of slider control, but with a rule that sum(B2:B5) is always 100.

This is to allow the user to give relative weightings to different items, on a percentage scale.

Is there any way to do this without using VBA?

Thanks for any advice,

Adrian

 

[GlennUK]

Yes, this is quite easy to do, although B2:B5 is 4 values not 5!

Put scroll bars in your sheet by using the Control Toolbox to draw them on your sheet, one scroll bar for each value to be manipulated. Draw the scroll bars so that are horizontal and cover columns E thru H 1 row high, so that they are level with each value they are going to control. For each scrollbar change the properties so that the maximum value is 100, and that the linked cell is either C2 or C3 or C4 or C5 depending on which row it is placed. End design mode by clicking the Exit Design Mode button.

Now you just need to put a few formulae in the sheet. In cell C6 put =SUM(C2:C5)
in cell B6 put 100
in cell B2 put =IF($C$6=0,25,C2/$C$6*$B$6) amd copy it down thru to B5.

If all the sliders are on zero you will get 25 in each of B2:B5, otherwise, the ratios of the sliders to one another will alter the values in B2:B5.

Good luck.

Cheers, Glenn.

 

[Chats]

Glenn,

Perfect, thanks for getting back to me so quickly, this does exactly what I need.

Cheers

Adrian