Tough One

[dantheram]

Hi all,

Here is my problem;

I have a graph with 5 different data sets which is controlled by a drop down menu. This drop down links back to an offset which changes the source data for the chart.

I wish the chart to display the data in the offset cells in descending order, at present it is locked to the order of the source data.

For example;

Stock Count (data set 1)

Stock a - 12
Stock b - 7
Stock c - 5

Sales Count (data set 2)

Stock a - 1
Stock b - 100
Stock c - 20

In the above when i switch from stock to sales my graph will follow the order above, i wish it shift the data into descending order automatically.

Is this possible?

 

[iliace]

You can use a LARGE function to make the offset work like you want, for example:

Excel Workbook
	A	B	C	D	E	F	G
1	Data Sets	1	2	3	4	5
2	Stock a	12	1	17	45	64
3	Stock b	7	100	3	199	64
4	Stock c	5	20	9	15	9
5
6		Data Set	1	2	3	4	5
7
8			12	100	17	199	64
9			7	20	9	45	64
10			5	1	3	15	9
sheet

 

[Steve=True]

Hi dantheram,

If I understand you correctly, you are looking to sort a chart based on the values of the Sales or Count.

Assuming your Categories are in A2 and the Value is in B2, you can copy and paste this formula into a range that you can chart:

=INDEX(A$2:A$4,MATCH(LARGE($B$2:$B$4,ROW($A1)),$B$2:$B$4,0))

It sorts the data in decending order and you can chart that area.

Steve=True
www.exceldashboardtemplates.com

 

[Damon Ostrander]

Hi dantheram,

Why not just tell the chart to display the values in reverse order? To do this, select the category (abscissa) axis > Properties > Scale tab > check the Categories in reverse order checkbox.

This reverse order property can also be set using VBA, so it could be called from your combobox's code.

Damon

 

[dantheram]

Thanks for all the solutions.

I made a work around using;

=MATCH(J3,OFFSET($A$2,$A$25,$A$26,11,1),0)

and then taking the value generated (1 to 11 for my 11 categories) and then employed choose to change the number back to the actual text value for the category.