Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort order for graph and not spreadsheet (dynamic)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort order for graph and not spreadsheet (dynamic)
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. -- Regards, Tom Ogilvy "Dharsh" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort order for graph and not spreadsheet (dynamic)
Tom Ogilvy wrote:
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort order for graph and not spreadsheet (dynamic)
"Tom Ogilvy" wrote in message
... 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 datapivot 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 -- Regards, Tom Ogilvy "Dharsh" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort order for graph and not spreadsheet (dynamic)
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. -- Regards, Tom Ogilvy "arno" wrote in message ... Tom Ogilvy wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort order on very large spreadsheet. | Excel Discussion (Misc queries) | |||
Can I save a custom sort order for a specific spreadsheet? | Excel Worksheet Functions | |||
how do I set up a excel spreadsheet to auto sort data in order | Excel Discussion (Misc queries) | |||
Sort order for chart only and not on spreadsheet | Charts and Charting in Excel | |||
Sort order for graph only and not spreadsheet | Excel Discussion (Misc queries) |