Copy/paste from many worksheets in the same spreadsheet into one r

[jaymesglew]

Now then, I'll try to be as detailed as possible as I find this all a little
complicated - so apologies if I'm not clear!

I have a spreadsheet that has 18 different worksheets - 17 of which are
responses by different companies to a questionnaire. In each worksheet, there
are several 'scattered about' cells which I want to copy and paste into a
neat row in the 18th worksheet, called 'Results'. These scattered cells will
be in the same place in each of the worksheets. There will be 17 rows of
'results' in the 'Results' worksheet. Bear with me!

To give an example, say we had XYZ Company's worksteet, named 'XYZ'. I want
to copy A12, A18, A32, A36, A67 and A69 into B1, B2, B3, B4, B5 and B6 of the
'Results' spreadsheet.

To make it a little more complicated, these cells currently contain SUM
formulas, and I need that formula to be replicated in the cells it is pasted
into - so that if I updated value A11 (and thus changing the total of A12) on
the XYZspreadsheet, the updated 'total' is reflected in the 'Results'
spreadsheet.

Any help - at all - appreciated, and I apologise for the messy description!

 

[Pete_UK]

I would suggest that you use B1 to contain the name of the sheet, i.e.
XYZ, then you can put these formulae in the cells stated:

B2: =INDIRECT("'"&B$1&"'!A12")
B3: =INDIRECT("'"&B$1&"'!A18")
B4: =INDIRECT("'"&B$1&"'!A32")
B5: =INDIRECT("'"&B$1&"'!A36")
B6: =INDIRECT("'"&B$1&"'!A67")
B7: =INDIRECT("'"&B$1&"'!A69")

Note the similarity in the formulae - just change the cell reference.
Note also the apostrophes - one after the first " and one before the !
- these allow the formula to cope with spaces in your sheet names.

Then put your other 16 sheet names in cells C1:R1 and copy B2:B7
across into C2:R7, then you will have your summaries from each sheet.
These will change automatically if the underlying cells change.

Hope this helps.

Pete

 

[Ron de Bruin]

See this example
http://www.rondebruin.nl/summary.htm