# Excel Collection sheet for data on multiple sheets

stubacka2 15:24 29 Jun 06
Locked

This is a direct follow on from the previous "renumber Excel worksheets" question.

I have (say)450 worksheets that are questionnaires of identical layout but with varying answers to 13 questions.

I need to summarise these 450 sheets onto one sheet to show only the score for each question on each sheet.

The data to be summarised from the 450 sheets(same cells on each sheet)are as follows:-

Question 1 = Cell M4
Q2=M8, Q3=M11, Q4=M14, Q5=M17, Q6=M21, Q7=M25, Q8=M28, Q9=M31, Q10=M34, Q11=M37, Q12=M44, Q13=M44

The Summary sheet would be as follows:-

Cells B2 to B14 would be numbered headings 1 to 13 (the questions on the sheets).
Cells A4 to A453 would be numbered 1 to 450 (the sheet numbers).

I now need to link each cell on the grid to the relevant cell on the relevant sheet. So for Sheet1, Question1 the summary sheet Cell B4 would be linked to Sheet1 Cell M4 and so on for all 13 questions on all 450 sheets.

I know I can manually link the cells using "=" and clicking in the relevant cells but this will take weeks and eventually I will need to do this for a total of over 5000 questionnaires in 8 different workbooks. I don't mean to sound lazy but I really don't fancy that task manually.

VoG II 15:37 29 Jun 06

I assume that you mean that C4 would be linked to Sheet 1 M4, and that you have renamed the sheets to 1, 2, 3 etc which will match the numbers entered in A4:A453.

In C4 enter the formula:

=INDIRECT("'"&A4&"'!M4")

then copy the formula down to C453.

You can use similar formulas for the other 12 questions.

VoG II 15:41 29 Jun 06

That doesn't look to clear - inside the brackets is:

<double quote> <single quote> <double quote> &A4& <double quote> <single quote> !M4 <double quote>

stubacka2 16:09 29 Jun 06

Apologies for the couple of typing errors giving wrong references but the formula worked perfectly.

Thanks again

This thread is now locked and can not be replied to.

Microsoft Surface PC release date, price and specs: All-in-one Surface PC to directly rival iMac

1995-2015: How technology has changed the world in 20 years

Best Photoshop video tutorials: 8 video tutorial websites for Photoshop

Apple's event invitations decoded: A look back at 16 of Apple's most cryptic invites | Clues in…