Copy Data from Same Cell on Several Sheets to a Summary Sheet

[necht_angel]

Hi there,

I would like to be able to copy data from the same cell in several different sheets, to create a summary sheet. Currently I am having to manually copy each cell into where I want the result to end up, but as I have many sheets and many cells to copy over I am looking for a quicker way. Hoping that there is a formula that will help me.

I have attached a small copy of the workbook with some notes on it to show in more detail what I mean.

If anyone is able to help I would be really grateful.

Thank you guys.

Kate

[PaulM100]

Try this: =INDIRECT("'"&A20&"'!"&"D3")
But in A20 keep only the sheet name. Same with the remaining ones.

[alansidman]

Kate

Here is a VBA solution:

Please Login or Register  to view this content.

How to install your new code

• Copy the Excel VBA code
• Select the workbook in which you want to store the Excel VBA code
• Press Alt+F11 to open the Visual Basic Editor
• Choose Insert > Module
• Edit > Paste the macro into the module that appeared
• Close the VBEditor
• Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

• Press Alt-F8 to open the macro list
• Select a macro in the list
• Click the Run button