Is there any chance of Microsoft improving “Scroll Bar” option in future version

[ThrottleWorks]

Hi,

Today I was working with “Scroll Bar”. Developer, Insert, Form Control, Scroll Bar.

I wanted the Scroll Bar value should start with number 41628 (number value of today’s date), since the maximum value accepted is 30,000 only I could not go further.

Not that I did not know these limitations of Scroll Bar but never used this function beyond 30,000 data point earlier.

Wouldn’t it be great if MS does following changes in the Scroll Bar.

1) Remove limitations for minimum and maximum value

2) Allow decimals also, at present it allows whole number only

3) Just as we have a Cell link for output, we should have cell links for input also, this will help to use formulas for generating minimum and maximum number

I think we could do it with VBA, not sure about it, but if we could get a improved Scroll Bar it will be much easier.

I was trying to post this in existing appropriate thread only, but could not find any, hence posting as a new thread.

Have a happy holiday season !

 

[crouchsw]

I'd just use a bias (whatever the date is for your first data point), and add that to your scroll bar output.

 

[ThrottleWorks]

@crouchsw , thanks for the suggestion.

I tried that, but it did not work perfectly.

I am not using Scroll Bar for the problem now, posted this as an observation.

Have a nice holiday season

 

[Hui]

You link the scroll bar to a cell
Then use that value and scale it accordingly
eg:
Say you want values between 1,000,000 and 2,000,000 with decimals
I would use two scroll bars
a single digit on the first scroll bar will increment by 33
and a Formula like
=MinV+((MaxV-MinV)*(SB#1/30000))

Then use the second scroll bar to increment between 0 and 33 which will include decimals
using: =(SB#2/30000)*((MaxV-MinV)/(30000))

Have a look at the example attached:

 

[ThrottleWorks]

@Hui Sir, thanks a lot, I also tried using combinations of 2 Bars, but could not go further.

I am trying your solution, will edit it according to my values.

Will share the results ASAP.

Sir, have a nice holiday season

P.S. - For a moment I was checking MINV as a function. :D

 

[r1c1]

I use scrollbars to scroll thru dates all the time. The trick is to use scrollbar value to offset from a given min date (in your case 41628). Since Hui and others already explained the technique, I will leave you a link with implementation.

See this:

http://chandoo.org/wp/2012/11/19/project-portfolio-dashboard-excel-template/

 

[ThrottleWorks]

@r1c1 , thanks for the link Keymaster, have a nice holiday.