PivotCharts, PivotTables, I'm going insane

Pardon my lengthy post, but I don't see an alternative to describing in
detail my quandary dilemma.

What I am hoping to accomplish:
Refresh/Update multiple tables and charts by changing one piece of data.
Whether this is possible, I do not know - please read on if you have time.

Project:
Take survey data and provide charts and tables summarizing responses by
total company response, response by department, and response by length of
service.

Data is organized as follows:
78 total questions were in the survey; each question is a column heading.
640 employees responded to the survey; each employee has a row.

76 out of the 78 questions have responses of agree, disagree, neutral,
strongly agree, strongly disagree.
The remaining two questions ask for the respondents department and length of
service (organized by less than one year, 1 to 3, 3 to 5, greater than 5)
respectively.

There are no numbers in this spreadsheet. It is all agree, disagree, etc.

For each question, I must provide:
1. a pie chart breaking down the total response to each question
2. a bar chart organized by department and response to each
3. a bar chart organized by length of service and response to each

Creating the individual charts is not a problem. However, for the sake of
saving time (not having to create 200+ charts individually), I was hoping to
figure a way to get the questions (first row of data) as a drop down or
something so I could simply create the charts/tables once for one question,
print, select question 2 from a drop down/update charts, print, select
question 3, update, print, etc.

It may be a matter of reorganizing the spreadsheet, I'm not certain.

I greatly thank you in advance, both for reading through this monstrosity of
a post and for providing any suggestions.

Best regards,
Brad

You could add a couple of columns to the survey results, to calculate
which question has been selected from a dropdown list. Then, base the
pivot table on those columns, and the Dept, and Years columns.

I've posted a sample file on my web site:

http://www.contextures.com/excelfiles.html

Under PivotTables, look for Survey Pivot Charts.

CBA88 wrote:
> Pardon my lengthy post, but I don't see an alternative to describing in
> detail my quandary dilemma.
>
> What I am hoping to accomplish:
> Refresh/Update multiple tables and charts by changing one piece of data.
> Whether this is possible, I do not know - please read on if you have time.
>
> Project:
> Take survey data and provide charts and tables summarizing responses by
> total company response, response by department, and response by length of
> service.
>
> Data is organized as follows:
> 78 total questions were in the survey; each question is a column heading.
> 640 employees responded to the survey; each employee has a row.
>
> 76 out of the 78 questions have responses of agree, disagree, neutral,
> strongly agree, strongly disagree.
> The remaining two questions ask for the respondents department and length of
> service (organized by less than one year, 1 to 3, 3 to 5, greater than 5)
> respectively.
>
> There are no numbers in this spreadsheet. It is all agree, disagree, etc.
>
> For each question, I must provide:
> 1. a pie chart breaking down the total response to each question
> 2. a bar chart organized by department and response to each
> 3. a bar chart organized by length of service and response to each
>
> Creating the individual charts is not a problem. However, for the sake of
> saving time (not having to create 200+ charts individually), I was hoping to
> figure a way to get the questions (first row of data) as a drop down or
> something so I could simply create the charts/tables once for one question,
> print, select question 2 from a drop down/update charts, print, select
> question 3, update, print, etc.
>
> It may be a matter of reorganizing the spreadsheet, I'm not certain.
>
> I greatly thank you in advance, both for reading through this monstrosity of
> a post and for providing any suggestions.
>
> Best regards,
> Brad


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra,

Brilliant.

I wasn't familiar with the OFFSET function. Worked like a charm. Your
sample sheet helped out tremendously. I had to study it a bit to see how you
were using named ranges, etc. At any rate, more information for my Excel
arsenal.

Many thanks!

Brad

"Debra Dalgleish" wrote:

> You could add a couple of columns to the survey results, to calculate
> which question has been selected from a dropdown list. Then, base the
> pivot table on those columns, and the Dept, and Years columns.
>
> I've posted a sample file on my web site:
>
> http://www.contextures.com/excelfiles.html
>
> Under PivotTables, look for Survey Pivot Charts.
>
> CBA88 wrote:
> > Pardon my lengthy post, but I don't see an alternative to describing in
> > detail my quandary dilemma.
> >
> > What I am hoping to accomplish:
> > Refresh/Update multiple tables and charts by changing one piece of data.
> > Whether this is possible, I do not know - please read on if you have time.
> >
> > Project:
> > Take survey data and provide charts and tables summarizing responses by
> > total company response, response by department, and response by length of
> > service.
> >
> > Data is organized as follows:
> > 78 total questions were in the survey; each question is a column heading.
> > 640 employees responded to the survey; each employee has a row.
> >
> > 76 out of the 78 questions have responses of agree, disagree, neutral,
> > strongly agree, strongly disagree.
> > The remaining two questions ask for the respondents department and length of
> > service (organized by less than one year, 1 to 3, 3 to 5, greater than 5)
> > respectively.
> >
> > There are no numbers in this spreadsheet. It is all agree, disagree, etc.
> >
> > For each question, I must provide:
> > 1. a pie chart breaking down the total response to each question
> > 2. a bar chart organized by department and response to each
> > 3. a bar chart organized by length of service and response to each
> >
> > Creating the individual charts is not a problem. However, for the sake of
> > saving time (not having to create 200+ charts individually), I was hoping to
> > figure a way to get the questions (first row of data) as a drop down or
> > something so I could simply create the charts/tables once for one question,
> > print, select question 2 from a drop down/update charts, print, select
> > question 3, update, print, etc.
> >
> > It may be a matter of reorganizing the spreadsheet, I'm not certain.
> >
> > I greatly thank you in advance, both for reading through this monstrosity of
> > a post and for providing any suggestions.
> >
> > Best regards,
> > Brad
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

You're welcome! Thanks for letting me know that it helped, and sorry
that I didn't have more time to add explanations to the file.

CBA88 wrote:
> Debra,
>
> Brilliant.
>
> I wasn't familiar with the OFFSET function. Worked like a charm. Your
> sample sheet helped out tremendously. I had to study it a bit to see how you
> were using named ranges, etc. At any rate, more information for my Excel
> arsenal.
>
> Many thanks!
>
> Brad
>
> "Debra Dalgleish" wrote:
>
>
>>You could add a couple of columns to the survey results, to calculate
>>which question has been selected from a dropdown list. Then, base the
>>pivot table on those columns, and the Dept, and Years columns.
>>
>>I've posted a sample file on my web site:
>>
>> http://www.contextures.com/excelfiles.html
>>
>>Under PivotTables, look for Survey Pivot Charts.
>>
>>CBA88 wrote:
>>
>>>Pardon my lengthy post, but I don't see an alternative to describing in
>>>detail my quandary dilemma.
>>>
>>>What I am hoping to accomplish:
>>>Refresh/Update multiple tables and charts by changing one piece of data.
>>>Whether this is possible, I do not know - please read on if you have time.
>>>
>>>Project:
>>>Take survey data and provide charts and tables summarizing responses by
>>>total company response, response by department, and response by length of
>>>service.
>>>
>>>Data is organized as follows:
>>>78 total questions were in the survey; each question is a column heading.
>>>640 employees responded to the survey; each employee has a row.
>>>
>>>76 out of the 78 questions have responses of agree, disagree, neutral,
>>>strongly agree, strongly disagree.
>>>The remaining two questions ask for the respondents department and length of
>>>service (organized by less than one year, 1 to 3, 3 to 5, greater than 5)
>>>respectively.
>>>
>>>There are no numbers in this spreadsheet. It is all agree, disagree, etc.
>>>
>>>For each question, I must provide:
>>>1. a pie chart breaking down the total response to each question
>>>2. a bar chart organized by department and response to each
>>>3. a bar chart organized by length of service and response to each
>>>
>>>Creating the individual charts is not a problem. However, for the sake of
>>>saving time (not having to create 200+ charts individually), I was hoping to
>>>figure a way to get the questions (first row of data) as a drop down or
>>>something so I could simply create the charts/tables once for one question,
>>>print, select question 2 from a drop down/update charts, print, select
>>>question 3, update, print, etc.
>>>
>>>It may be a matter of reorganizing the spreadsheet, I'm not certain.
>>>
>>>I greatly thank you in advance, both for reading through this monstrosity of
>>>a post and for providing any suggestions.
>>>
>>>Best regards,
>>>Brad
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html