Help - Excel Summary sheet

[Ric]

Hi

I have produced an Excel workbook for gathering exam results and giving a
score to them, counting the number of passes over a certain level etc. Each
sheet is one student and I have used a little macro to name the sheet
according to the name entered at the top of the sheet.

My summary sheet requires certain formulae to analyse results from each
worksheet.

The problem is, I can easily enter the row of formulae for one student - but
there could be up to 50 in a workbook! The fill action does not increment
through the sheet names either when they are re-named or when they have
original names. Is there a way of making it do so?

Example (open post to full screen here)

Ann Brown | ='Ann Brown'!D40 | =SUM('Ann Brown'!D39/'Ann Brown'!D41) |
=SUM('Ann Brown'!D39+'Ann Brown'!D54/'Ann Brown'!J54) |

etc

If the formula in second column (etc) could pick up the name from first
column it would help...

Hope you understand all this and can offer some ideas...

Thanks

Ric

 

[Earl Kiosterud]

Ric,

Here's an approach. Create a sheet "Start" and "End". Have the student
sheets between those two. You can hide Start and End; they're just
placeholders of sorts. This macro will copy B2 from each student sheet into
it's respective row in sheet "Summary". It's by no means a finished
project. For one thing, i needs to be adjusted so it goes in the row you
want in Summary. But I think it will give you the ideal.

Sub TransferStudentData()
StartSheetIndex = Sheets("Start").Index - get number of sheet "Start"
EndSheetIndex = Sheets("End").Index
For i = StartSheetIndex To EndSheetIndex
Sheets("Summary").Cells(i, 2) = Sheets(i).Range("B2")
Next i
End Sub