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.

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.

Hi Eric,
I'm not too sure that what I have to offer is what you are after but
I'm not about to let that stop me:-)

I pasted your sample data into columns A and B, then I typed the
following formula into D1, but it could be in any spare column...

=OFFSET(A$1,MATCH(SMALL($B$2:$B$51,ROW(A1)),$B$2:$ B$51,FALSE),0)

I then filled this formula down to row 51.

the $B$2:$B$51 gives enough space for 50 Names, more than enough.


Next, in E2 I typed the formula...

=SMALL($B$2:$B$51,ROW(A1))

filling down to row 51 again.

These formulas result in the data from columns A and B appearing in
columns D and E in ascending order based on the value in column B.

You can then use columns D and E as the Source data for your chart.

I know this isn't a complete solution to your problem, you still have
the problem of selecting the correct number of rows from columns C and
D so that your chart's horizontal axis is not too long, etc.
..

Hope you get some use out of this
Ken Johnson

Sorry Eric, it was too good to be true. My formulas only work properly
when no two scores are the same, so it's back to the drawing board for
me.

Ken Johnson

Hi Eric,
I've solved the problem created by identical scores.
I've now got a single worksheet with column A for names, up to 20
names.
Columns B to I are for the 8 different sets of scores.
Columns J to Q are all hidden and are helper columns that solve the
duplicate scores problem.
Column S automatically shows the Test 1 scores in order of increasing
size down the column, to the left of it is column R with the names for
those scores.
Then similarly for the other 7 test scores and corresponding names in
column T & U,
V & W, X & Y, Z & AA, AB & AC, AD & AE, AF & AG.
I then have used dynamic named ranges for each of the 8 chart sheets so
that each chart automatically shows the correct number of names on the
horizontal axis, plus the scores occur in increasing order from left to
right.
I've used Conditional formatting to improve the appearance and sheet
protection (no password) to protect the cells with formulas.
I hope to finish sometime tomorrow. Then I'll include a link here if
you are interested.
Thanks for posting your problem it has inspired me to put together a
graphing tool that I will be using myself in my work as a high school
science teacher

Ken Johnson