Collating variable sets of data into a summary sheet

zakynthos

Board Regular
Joined
Mar 28, 2011
Messages
169
Hi,

With Excel 2010:

On a Summary workbook (Sheet 1) in column A:

A fixed list of 12 categories, each with a fixed number of associated questions listed below each Category heading

Category1
Q1
Q2
Q3 etc

Category2
Q1
Q2
Q3
Q4 etc

I also have 2 types of weekly data sheet formatted as follows:

Data Sheet 1:

In Column A:

A variable list (ie repeats of the above 12 categories), each with a number of associated questions in the following format:

Example in A1, A2 A3 A4 etc
Category1 / Question2
Category3 / Question5
Category1 / Question1
Category2 / Question4 etc

Data Sheet 2:

In Columns A and B:

A variable list (ie repeats of the above 12 categories, each with a number of associated questions in the following format:

Example in A1, A2 A3 A4 etc
Category1
Category1
Category 4
Category 6

Example in B1, B2, B3 B4 etc
Question5
Question2
Question4
Question6
Question5

What I would like to be able to do is link the results of these 2 sets of calculations into the summary spread sheet.

Calculation 1 (example)

Say I have 1960 repeated categories/questions in all over the 12 fixed categories of which there are 16 repeats of Category 1. I would want to return a value of 1% (rounded up) against Category 1 on my summary sheet (say C1)
(i.e. 16/1960*100 = 0.82% rounded up to 1%

Calculation 2 (example)

Say if I have a count of 8 Question 1’s within Category 1 then I would want to return 50% against Q1 of Category 1 (say in C2) on the summary workbook sheet.

(ie =8/16*100 = 50%) on the summary sheet.


Many thanks:)
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Watch MrExcel Video

Forum statistics

Threads
1,130,117
Messages
5,640,206
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top