Sort order for graph and not spreadsheet (dynamic)

[Guest]

Hi there! I neet to plot a simple graph with the data below

Candidate Score
A 4
B 3
C 7
D 1

The relevent numbers (are actually scores) keep getting updated for each
candidate. I want my graph to plot it in the order of highest score to
lowest (whilst keeping my spreadsheet in the same order A,B,C,D . . .)
every time I update the score (automatically) without me having to change it
everytime I do it.
I have 32 entries. Can I use a macro to do this
Can anyone pls help me plssssssssssss

Thanks so much in anticipation

Dharshanie

 

[Tom Ogilvy]

To the best of my knowledge, a graph can reverse the order, but it can't
sort. So the solution would be to put formulas in another location that
produce a sorted list and build your graph from that. This list could be on
a hidden sheet as an example.

 

[arno]

To the best of my knowledge, a graph can reverse the order, but it
can't sort. So the solution would be to put formulas in another
location that produce a sorted list and build your graph from that.
This list could be on a hidden sheet as an example.

what if the second range is sorted automatically by an worksheet-change
event? would that change the chart, too?

I thought about using formulas as you mentioned (eg. with rank),
however, what if two have the same rank?

arno

 

[Damien McBain]

To the best of my knowledge, a graph can reverse the order, but it can't
sort. So the solution would be to put formulas in another location that
produce a sorted list and build your graph from that. This list could be
on
a hidden sheet as an example.

Or use a pivot table/chart.

If you have a table with 2 columns entitles "Candidate" and "Score":
Cretae a pivot chart/table by selecting one cell in the data table and going
data>pivot table and pivot chart report in the menus.
Select Pivot chart with pivot table then Next
Next if the data range is ok
Layout
Drag "Candidate" to the row section and "Score" to the data section
Double click the "Candidate" field that you have dragged into the "Row"
section. Choose advanced and change the autosort to "ascending" and the
field to sort to "Score" (it will probably say "sum of score", this doesn't
matter if there's only 1 score for each candidate).
Then OK, OK, OK, finish.
This will give you a pivot table and chart (bar chart by default, you can
shange it) sorted by "score" in ascending order.
you can then format it to suit.
To refresh the data when you change it just right click the pivot table and
select refresh or make a button on the pivot sheet with;

Sub DeodoriseMyPivotTable()

ActiveSheet.PivotTables("<whatever you pivot table is
called>").PivotCache.Refresh

End Sub

HTH

Damo

 

[Tom Ogilvy]

what if the second range is sorted automatically by an worksheet-change

event? would that change the chart, too?

Yes it would.

The limitations of Rank can be overcome.