Need Help - Scrollbar in Vertical Barchart

[MSC Bobs]

Hello everyone,

I participate in a game with more than 200 participants and each week we publish updated standings.

I've created a vertical barchart and have inserted a scrollbar using tutorials found in many places on the Internet.

My problem lies somewhere in the scrollbar formatting and I have not been able to figure it out and I'm hoping somebody here can help me pinpoint the error and suggest a fix.

The problem is that I would like each click to show 25 places. The initial view shows places 1 through 25 in the standings. When a user clicks on the scrollbar, I would like the chart to show the next 25 in the standings, which would be places 26 through 50, and so on.

Thank you!

 

[Syedali]

PFA

 

[MSC Bobs]

Thanks, Syed. I appreciate the quick response.

However, it's not quite what I'm looking for because I'm looking for the axis and the bars to remain static and not resize every time the scrollbar is clicked.

I'm using named ranges (they are included in the file I uploaded) with the OFFSET function and the offset does not seem to be moving in accordance with each scrollbar click.

 

[MSC Bobs]

Syed,

After monkeying around with the sub datasheet you suggested, I was able to make it work the way I wanted except for one detail.

I want to keep the bars the same length as if I am looking at the entire chart, instead of looking at the chart one page at a time.

Is it possible to lock in the size of the bars relative to the population so that they do not resize every time the scrollbar moves?

 

[Syedali]

Syed,

After monkeying around with the sub datasheet you suggested, I was able to make it work the way I wanted except for one detail.

I want to keep the bars the same length as if I am looking at the entire chart, instead of looking at the chart one page at a time.

Is it possible to lock in the size of the bars relative to the population so that they

Sorry i don't know. I Search and find. But definitely OUR Ninjas will answer for the question.

 

[Khalid NGO]

I want to keep the bars the same length as if I am looking at the entire chart, instead of looking at the chart one page at a time.

Is it possible to lock in the size of the bars relative to the population so that they do not resize every time the scrollbar moves?

Hi,
There may be other ways, but simple solution would be setting up a fixed length of text; for example replace H2 with this formula:

=LEFT(VLOOKUP($G2,$A$1:$C$213,2,0),8)
you can change 8 to your need.
copy down till H26

You will not able to see complete text.
i.e. 19th Hole Experts
will become 19th Hol
that means your text length will be 8 chars.

Now try scrolling your chart, and see the results.

Regards,

 

[MSC Bobs]

I've spent the better part of the day monkeying around with this and have sort of come to the solution I was looking for, but there are still some issues I have.

The issues I have now are as follows:
1. Each time the scrollbar moves to a new page, the formatting defaults to the original setting when the chart was first created. You need to format the chart as if it's a brand new chart every time you click the scrollbar to change pages.
2. Each time the scrollbar moves to a new page, the data label disappears even if you select Value From Cells in the Format Data Labels page and use an OFFSET formula.

Short of using VBA, does anybody know of any work arounds to solve these issues?

I've attached the updated file if anybody cares to see the scrollbar working in the vertical bar chart. It uses OFFSET formulas that can be found in the Name Manager.