G
Guest
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 we’ll 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 won’t 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 haven’t 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.
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 we’ll 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 won’t 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 haven’t 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.
