Need to pull values from different sheets by referencing adjacent names...

[vanhoand]

I'll try to make this as straightforward as possible. Using Win7, Office2010.

I have a spreadsheet with multiple worksheets (for different days) that lists the names of students and then a total amount due for each student next to those names for each day. The names in each spreadsheet are not always the same and are not always in the same order. In a separate worksheet, I need to sum the total due for each individual name from all of the other worksheets.

So for example, if I'm trying to sum all the amounts due for Jane Doe from each worksheet, I can't just pull and sum values from B5 (assuming that's where she is listed in the 1st worksheet) because on subsequent sheets, her total due for that day might be down at B7 or some other cell in that column. It will be in the same column though.

I know I could filter each worksheet alphabetically to at least get all the names in a common order, but that solution doesn't work if the exact same number of student names are above her in each worksheet (which would be unlikely).

What would be the best way to do this? I'm guessing a script would be ideal but I'm a novice in terms of writing them.

Thanks!

 

[jbeaucaire]

A pivot Table makes short work of summing values together, and the source ranges for a Pivot Table can be on multiple sheets.

Consolidate multiple worksheets into one PivotTable report - Excel

 

[vanhoand]

OK thanks. I'll look into those. Are there different types or something more specific I should look at?