Cut and Paste Data from Multiple Worksheets to One Worksheet

[uknow message board]

Hi!

I am a teacher and grade tests using excel. I have a master template
of my MC test scoring sheet and copy a worksheet for each student and
input their data.

Once all their answers are inputted, formulas spit out their raw score
and percentile, as well as their percentile on all of the tested
subject areas of the test (if we were taking a math class for example,
it would give students their percentile rank separately for addition
problems, subtraction problems and so on).

The problem I'm having is created a non-time intensive class summary
worksheet. I'd like to have an easy way to have the data from each
individual students worksheet automatically link to a score analysis
worksheet, so I can see what the entire class' scores are (also I'd be
able to average scores and find the high and low scores, etc) as well
as analyze what particular subject areas are causing students the most
trouble.

Right now, I do it the hard way [='John Doe!'$A$47], which requires me
to type out the student's name (which is the name of their worksheet)
for every field I want to compile.

There has got to be a faster way, right? I'm sure there is some
complicating programming/macro way, but I'm wondering if there is some
variation of Names or 3D References or even Shift selecting all the
student worksheet tabs that would do the trick.

Thanks in advance for your help!

Jaime

 

[HS Hartkamp]

Build a list of cell-references (based on parameters elsewhere) and use the
INDIRECT function referring at list items. If the output of your students is
always in the same cells, you can use some formula to make it easier

Example:

A1 contains the path to all student-sheets (e.g. "C:\Data\Excel
results\Test1\

B2 contains "A47"
C2 contains "D53"

A3 contains "John Doe"
B3 contains =INDIRECT("['"&$A$1&"Sheet1!"&$A3&"'B$2"&"]")

Copy cell B2 down and right to get the desired values copied from all
worksheets. Check the exact syntax for the pathname by referring to a single
cell in an external worksheet, and then closing that sheet. Build the string
to that example, and when it's OK, include the indirect function. Excel is
very picky with the order of [, ], ', and !

Bas Hartkamp.

 

[uknow message board]

Thanks for the help!

I ended up using this formula to make the workbook:

=INDIRECT("'"&$B4&"'"&"!"&I$2)

Where B4 is where the student's name is scored and I2 (that's an i in
front of the 2) references the cell where the data will be pulled from
each student's worksheet.

And you were right, it took a while to get the ' and " located
correctly for the function to work.

Jaime