Scroll Bar Chart

[Thomas Kuriakose]

Dear All,

Kindly help with the attached scroll bar chart, I am trying to make after reading scroll bar charts, but could not succeed.

What we need is on scrolling the dates the location values should be displayed in the chart and on selection of the combo boxes it should show respective data.

I tried inputting the dates but the dates are of a varying range.

Thank you so much.

with regards,
thomas

 

[MaunishP]

Hi Thomas,
Small edition done to your vlookup : Instead of =VLOOKUP($AA$10,$A:$L,AB$5,0) $ which is placed before cell number 10, I removed it and it worked.

Please check and let me know.

Regards,
Maunish Patel

 

[Thomas Kuriakose]

Dear Maunish,

Thank you so much for your kind help. Check and found location AA is not giving correct values.

Secondly can we see all the locations while scrolling with all selection in combo box

and if we need to see the locations date wise do we need another chart, as there is a combo box for date wise selection also.

Thank you so much,

with regards,
thomas

 

[Hui]

see attached:

 

[Thomas Kuriakose]

Dear Hui,

Thank you so much for the solution provided, but the scroll bar is not working.

When we use the scroll, it should change locations and the bar chart.

Also if we need to check the locations by date, do we need to create another scroll bar or with the existing scroll bar we can navigate on both charts.

Kindly help.

Thanks so much once again.

wit regards,
thomas

 

[Hui]

Should the scroll bar change the start date or the duration?

If it is start date, what is the drop down (date) for in the chart?

 

[Thomas Kuriakose]

Dear Hui,

The scroll bar should be based on week number based on the dates.

Thank you so much for your kind help.

with regards,
thomas

 

[Hui]

In my previous model, simply link the Scroll Bar to $R$5 instead of $Q$7

Or see attached file:

 

[Thomas Kuriakose]

Dear Hui,

Thank you so much for this great help. Thanks a ton for this.

To make this look better -
May I ask whether we can carry out this with reference to week number and the corresponding year instead of using the dates provided.

for example 34-2016 (week 34, 2016)

The bar width is very small, do we need to change the chart type to get a better representation, I tried changing the gap width, but it is not increasing.

Thanks once again for your help on this.

with regards,
thomas

 

[Thomas Kuriakose]

Dear Hui,

Correct me if I am wrong, on selection of the location the chart should only display the location with the specific date/week number and year to make the chart look good.

Currently it is displaying a lot of dates and the format is not good.

Can we have two charts with using the same scroll bar - one with the location selection and the other with the date/week number and year selection.

Thanks you so much,

with regards,
thomas

 

[Hui]

Thomas

A few points

1. You can have charts display what ever you want, but you have been very unclear about what each chart should have and the duration of each chart has never been mentioned, so I have assumed you wanted all the data

2. The X-Axis format of the current chart is Date,
Right click on the X-Axis Format Axis
Change Axis Type to Text Axis
That will fix the column width to some extent

Then right click on a data series in the chart
Format Data Series
Set the Gap to a smaller values

3. Week Numbers, What is the format of the Week Number
Look at the Excel Weeknum() function and let me know what values are applicable to you

When setting up a dashboard the first thing you need to do is specify what data you want to see
Over what duration
In what format
In what chart type

What is your data in Columns AB:AM for ?

 

[Thomas Kuriakose]

Dear Hui,

Apologies for not providing the right information. actually I tried using the examples available in the net to first do it myself, but I failed.

The data itself is confusing me. The example which I read and tried to copy is attached for your reference.

However, what is required from this data is -

1. The dates provided are the delivery dates for shipments for different locations.
2. The logistics person needs charts to check deliveries location wise and date wise.
3. The date wise can be changed to weeks, I checked the net again and came across this formula, but the year is not getting added to the week.
=INT((S13-DATE(YEAR(S13-WEEKDAY(S13-1)+4),1,3)+WEEKDAY(DATE(YEAR(S13-WEEKDAY(S13-1)+4),1,3))+5)/7)

4. On clicking the Location, the chart should show only one bar with the delivery date or week.
5. On clicking the delivery date/week, it should show the locations for that delivery date/week.
6. We also need the scroll bar to move and check the complete delivery and location schedule.

Sorry for asking too much, I am working daily on this and still not able to figure out how to get this done.

There are very good scroll charts in the net with named ranges and offset function, but this is beyond my head.

Thanks you so much for your support on this.

with regards,
thomas

 

[Hui]

I have added some more data as well as a WeekNum Column to the data
It has the format of YY-Wk
But you will see how easily it can be changed


What do you want to do with the Weekly data?

 

[Thomas Kuriakose]

Dear Hui,

Thank you for this, but this file I attached was to inform you that my original file was worked out based on this example.

I need my original scroll bar chart file to be like this.

Kindly help with he original file I sent on 07.04.2015 (Tuesday).

Thank you so much.

Apologies for taking so much time on this.

with regards,
thomas

 

[Hui]

What do you want to happen when there are multiple entries on 1 day or in 1 week ?

 

[Thomas Kuriakose]

Dear Hui,

The multiple entries of locations should be visible on the chart when the selection is by dates/week.

Where the selection is by Locations, the chart should display multiple days if there are relevant points.

Thank you so much

with regards,
thomas

 

[Thomas Kuriakose]

Dear Hui,

After much thought and changing some data, I prepared a pivot summary with slicer to get the chart I need.

But this is not what we need, we need a scroll chart which should work like this pivot summary on scrolling the scroll bar.
1. Three charts to be operational with one scroll bar.
2. Combo box in each chart with the following -
(a) Filter by SKU - result in chart dates/weeks-yy and locations.
(b) Filter by Locations - result in chart dates/weeks-yy and no of skus
(c) Filter by Dater/weeks-yy - result in chart locations and no of skus.

Thank you o much.

with regards,
thomas

 

[Thomas Kuriakose]

Dear All,

I have tried to create the scroll bar chart in the attached file, but I an not successful with the combo box function.

There are three charts operational with one scroll bar, but I need three combo box in each of the charts with the following functions-
(a) Filter by SKU - result in chart dates/weeks-yy and locations.
(b) Filter by Locations - result in chart dates/weeks-yy and no of skus
(c) Filter by Dater/weeks-yy - result in chart locations and no of skus.

The chart title of Locations should change when combo box selection is changed.

Kindly help on this.

thanks,

with regards,
thomas

 

[Hui]

I will have a look at this in the next few days

 

[Thomas Kuriakose]

Dear Hui,

Kindly help with the chart solution.

Thank you so much.

with regards,
thomas

 

[Hui]

Thomas

Can you please post the file with the data

Please specify what you want on the X Axis as it seems to change from dates to week no's. Be specific about this as it is important.

Please be specific about what charts types you want and what data you want on each chart

Specify what you want filtered etc

Please put it all in 1 post so that there is no confussion about what you want