Help, please: sorting the Legend

[Henry]

I'm lucky if I use 10% of the power of Excel. I would be grateful for
advice.

Ten salesmen on a team. At the end of each month, their cumulative
totals for the year are updated. The spreadsheet gets re-sorted, showing
their standing in rank order. Simple enough.

At the beginning of the year, I created a line chart to track each
person's progress. I initially listed the names in alphabetical order
and defined the series accordingly. The problem comes when I do the end
of the month re-sort. The names in the Legend are no longer in
alphabetical order but neither do they correctly correspond to that
person's correct row in the spreadsheet, as represented in the line
graph.

How do I make the names in the Legend appear in the same order as in the
sorted speadsheet, and with each name corresponding to the correct line
in the graph?

Thank in advance.

cheers,

Henry

 

[Don Guillett Excel MVP]

I'm lucky if I use 10% of the power of Excel. I would be grateful for
advice.

Ten salesmen on a team. At the end of each month, their cumulative
totals for the year are updated. The spreadsheet gets re-sorted, showing
their standing in rank order. Simple enough.

At the beginning of the year, I created a line chart to track each
person's progress. I initially listed the names in alphabetical order
and defined the series accordingly. The problem comes when I do the end
of the month re-sort. The names in the Legend are no longer in
alphabetical order but neither do they correctly correspond to that
person's correct row in the spreadsheet, as represented in the line
graph.

How do I make the names in the Legend appear in the same order as in the
sorted speadsheet, and with each name corresponding to the correct line
in the graph?

Thank in advance.

cheers,

Henry

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

 

[Pete_UK]

One way is NOT to sort the original master sheet. Instead, take a copy
of that sheet (using CTRL-drag), give the copied sheet a different
name and then sort the copied sheet. The original links will still
refer back to the original master sheet, and so will not cause you the
problems.

Hope this helps.

Pete

 

[Tushar Mehta]

Don't sort the data sheet. Instead, create PivotTables (and
PivotCharts where appropriate) Sort these alphabetically or by
cumulative totals or whatever other criteria you want to use.

I'm lucky if I use 10% of the power of Excel. I would be grateful for
advice.

Ten salesmen on a team. At the end of each month, their cumulative
totals for the year are updated. The spreadsheet gets re-sorted, showing
their standing in rank order. Simple enough.

At the beginning of the year, I created a line chart to track each
person's progress. I initially listed the names in alphabetical order
and defined the series accordingly. The problem comes when I do the end
of the month re-sort. The names in the Legend are no longer in
alphabetical order but neither do they correctly correspond to that
person's correct row in the spreadsheet, as represented in the line
graph.

How do I make the names in the Legend appear in the same order as in the
sorted speadsheet, and with each name corresponding to the correct line
in the graph?

Thank in advance.

cheers,

Henry

Regards,

Tushar Mehta
Microsoft MVP Excel 2000-present
www.tushar-mehta.com
Excel and PowerPoint tutorials and add-ins