Spinner Control: user defined Min & Max & increment values

[Maria]

Hello:
I would appreciate help on this one from you experts.


I am using a SPINNER CONTROL on Worksheet to change value of a target cell.
I do not want to use the Minimum, Maximum or incremental value provided with
the control but instead want to use something customized for the following
reasons
1) The minimum & maximum values for the Spinner control that I need to use
are not fixed but keep on changing since these values are derived from cells
A1 to A4. The spinner control does not allow reference to cells as its
minimum & max values and hence I am stuck at this stage.

What I would like to happen as the spinner control arrows are clicked, is
for the values of the target cell to change sequentially from A1 to A2 to A3
to A4. The target cell that I am using is cell B1

2) The second reason is that the min, max & increment values in Spinner
control are all whole numbers, while many of the values that I want to have
entered in the target cell B1 are less than 1.

Would sincerely appreciate your help
TIA

 

[Dave Peterson]

Maybe you could use a helper cell.

I put some data in A1:A4 (.4, .2, .8, .6 just to be different).

I put a spinner from the forms toolbar on the worksheet and I gave it a minimum
value of 1 and a max of 4 and a current value of 1.

I assigned B1 as the cell link (I can use that in a formula later).

Then in C1, I put this formula:
=INDEX(A1:A4,B1)

If you ever clear contents of B1, you may want:
=IF(B1=0,A1,INDEX(A1:A4,B1))

Then the spinner could change C1 by cycling through A1:A4.

 

[Maria]

Hello Dave:
What a perfect solution. Exactly what I was looking for, worked like a
charm!
Thank you so much