How to do reference particular cell to a page in excel?

Not entirely sure what you're asking for but try

=ROW()/33+1

in your Result sheet column B Roll Number cells.

Then in for example B5 use
=INDIRECT("'DZO-I'!C"&B1+6)

However for the very reason that you've found in that it's not straightforward to summarise your results, you are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.


A lot of people start by designing the form that they expect to see as the final report, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it, Yours exhibits all those features.


You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

If you look at your course sheet they are all essentially the same layout. You should therefore create a single database sheet much like your current course layout but with an additional column which identifies the Course. You then only need to add a persons record where they actually attend the course.

Hi,

This workbook is subtly different to the previous one. The second Roll Number is now on row 32 not row 33 as before
Your B1 cell still shows
=Information!A7 which means that you haven't changed it as I suggested.

Using your original file
B1 should be
=Round(Row()/33+1,0)

B33 should be
=Row()/33+1

When you copy B33 to B66 etc.. the roll number will increment by 1

Then you need to use the B5 formula I gave you and copy it to the other reports. If your sheet names exactly replicated the labels in column A you could use the A column value in the Indirect function instead of the actual sheet name. This would allow you to copy the Indirect formula across the sheet as well as down.


You have not changed it to