Pushing a 'slider' to the limit

[naquinn]

Hello everyone,

I have a question about sliders.

I have a column with different values in it, all numbers. I have a separate cell that calculates a range within that column. Say for example, =Average(A1:A12)

Ok, my question is, can I design some sort of 'slider' that expands or contracts the range that this simple average formula is looking at? So by moving the slider a number of clicks, the formula suddenly becomes =Average(A1:A20)

My head hurts from trying to work this one out. Someone, said it might be possible with VBA? But no idea what this means.

Anyway, help, solutions, or pointers in the right direction would be very very gratefully received!

Many thanks to you all!

nick (london)

 

[tusharm]

From the Forms toolbar insert a scrollbar onto the worksheet surface.

Right click the control and select Format Control... Set the linked cell to a cell that will contain the row number of the last cell of interest. I chose B3. In B2, I entered 1, the row number of the first cell of interest.

Then, in some cell enter the formula =AVERAGE(INDIRECT("A"&B2&":A"&B3))

That's it. You are done.

Right click the control and select Format Control... to adjust the different parameters. I'd set the min. value to 1 (rather than the default 0) and the max. value to whatever is appropriate to your model.

 

[naquinn]

Wow! Thankyou!

A simple and elegant solution.

Many many thanks!

nick

 

[tusharm]

You are welcome.