# Excel Collection sheet for data on multiple sheets

[DELETED] 15:24 29 Jun 2006
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.

[DELETED] 15:37 29 Jun 2006

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.

[DELETED] 15:41 29 Jun 2006

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

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

[DELETED] 16:09 29 Jun 2006

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.

Elsewhere on IDG sites

Best computer security tips

This fantastic short film is packed with hidden TV and film references

Best external graphics cards (eGPUs) for Mac

Test : les écouteurs Bluetooth Soundcore Spirit X d’Anker