Eric:
Here is how I wld do it.
1. Give every possible entry a number 1-n (so Ann=1, Bob=2,etc). Put this
in col A. (say 1-100 in a2:a101, or any max that's relevant)
2. Use RANK to sort your scores, and place this in a col B. If yr entry
names are in C and scores in D it is (in cell B2: RANK(D2,$D$2:$D$101) (in
case n=100) and copy downwards
3. Use VLOOKUP to resort the entry names in col E, and the corresp score in
col F
(in E2: VLOOKUP(A2,$B$2:$D$101,2,false); in cell F2:
VLOOKUP(A2,$B$2:$D$101,3,false)
You now have a sorted rank in col E and F

Use the familiar dynamic range name to ignore non-existing names in yr chart
(Using OFFSET).
Alternatively, but less elegant, is to make sure blanks are replaced by
#NA() as Excel will ignore these cells when charted (Use in E2:
IF(Countblank(c2)=1,NA(),VLOOKUP(A2,$B$2:$D$101,2, false) and similar to F2.
Copy downwards.
In yr Worksheet2 just refer to col E + F (full range 2:101) instead of A and
B in yr example and it shd work. You dont need anymore to put in names in
the middle of the range. You could make it work more elegantly by putting
the ugly help rows/cols in a different sheet and hiding this, which I wld
prefer if the template is used by others as well.
I hope this helps.
(Henk)

"Eric D" wrote:

I am creating an Excel template that will be used to display numerical survey
results for groups of people. The template has several worksheets. Worksheet1
contains a table of names and survey results. The table has eight columns of
numerical data but for simplicity sake well assume the table has just one
result for each person:
A B
1 Name Result
2 Ann 12
3 Bob 9
4 Joe 22
5 Max 17
6 Sam 33
7 Sue 29

Worksheet2 displays a vertical bar chart. The labels along the X axis of the
chart consist of the names in Column A of Worksheet1; the range of the Y axis
is from 9 to 36; and each person's Result from Column B is displayed as a bar
above his or her name. Simple enough. However, I want to sequence the data in
the chart left-to-right by result, so the name with the lowest result appears
in the left-most position in the chart and the name with the highest result
appears in the right-most position in the chart. (This is complicated by the
fact that we want to do this eight times, once for each of the eight columns
of survey results, but we wont deal with that here.)

One way to do this is to create a table in Worksheet3 by using references to
the table in Worksheet1, sort the data in the table in Worksheet3 by Result,
and generate the bar chart from the table in Worksheet3.

However -- and this is the problem -- we want to use this template to create
spreadsheets for multiple groups that will have different numbers of members.
For example, one group will have 8 members, another will have 12, and a third
will have 6. If we don't need to sort the values in the chart in Worksheet2
by result, we can generate the bar chart from the table in Worksheet1; then
we can insert or delete rows of names and results in the middle of the table
in Worksheet1 and the bar chart in Worksheet2 will automatically adjust to
reflect the larger or smaller number of members. We have been doing this for
a few years and it works flawlessly. However, if we use Worksheet3 to sort
the data, the references in Worksheet3 do not change to reflect the inserted
or deleted rows in Worksheet1: if rows are added to the table in Worksheet1,
they are not added to the chart; if rows are deleted, reference errors are
generated.

I am hoping to find some way to identify the range of values in Worksheet1,
such as a named range that specifies the beginning and ending rows in the
range; replicate the values in the range in an array; sort the array by
Result; and use the array to generate the bar chart. Presumably, if rows are
added or deleted in the middle of the range, the array would be adjusted
automatically and hence the chart would correctly display the names of all
the group members sorted by Result. I havent found the Excel functions to do
this. Conceptually, the logic would be something like this:

1. Create array €œSortedbyResults from Worksheet1!A2:B7
2. Sort €œSortedbyResults by Column B
3. Generate bar chart from €œSortedbyResults

For this logic to work, the array would have to be defined by anchoring it
in the top left and lower right corners of the table in Worksheet 1. If rows
were added or deleted from the table, the lower right anchor would
automatically move accordingly.

I will be grateful for any pointers I can get. Please be as specific as
possible, because I have never used arrays in Excel before.