Linking data easily from one excel template

Question

Hi.  I have approximately 60 excel spreadsheets, all in the same format, from which I wish to extract only certain data.  The extracted data is to go into a summary sheet and there will be one summary sheet per excel data sheet.  In order to extract the data I have set up a template (the "summary sheet")  linking cells to the required cells in each data sheet, but in order to complete the remaining 59 summary sheets I have to re-point all of the linked cells in the template to each separate data sheet. This will be time consuming.  Is there an easier way of completing this task ?  I'm using MS Excel 2007.  Many thanks.

Answer 1

An idea which springs to mind is to use INDIRECT for great ease of formula propagation 

In your summary sheet:

In B1 across, list the sheetnames, eg: Sheet1, Sheet2, etc

In A2 down, list the specific cell references to link to in each sheet, eg: B2, E5, K9, etc 

Place this in B2: =INDIRECT("'"&B$1&"'!"&$A2)

Copy across and fill down to populate it all in a single smooth stroke

---

You can run the sub below (in a new sheet) to easily list all 60 sheetnames in one swoop. Then just copy the sheetnames n paste special > transpose into B1 across in your summary sheet

Sub ListSheetNames()

    Dim wkSht As Worksheet

    Range("B2").Select

    For Each wkSht In Worksheets

       Selection = wkSht.Name

       ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate

    Next wkSht

End Sub

Answer 2

Max, I like the idea - a lot.  I'm only concerned that the OP is referring to workbooks rather than worksheets when they say "spreadsheet".  Although something similar would work even for workbooks just as easily if they were all in the same folder.

Answer 3

Whether or not a macro would be really beneficial to you for this kind of one-time deal is dependent on how much additional work might need to be done to set up the links.

If all of the 60 spreadsheets (separate workbooks??) are in the same folder and if the data to be extracted from each is on sheets with the same name, and in the same addresses, then while it would still take some time, you could do it from the keyboard fairly easily:

With your one sheet set up to get data from one of the 60 spreadsheets;

right-click the sheet's name tab and choose Move or Copy and check the "Copy" option and make a copy of the worksheet, then

Select the new sheet, give it a new name if you want, or not -

Then use Edit --> Replace to change the name of the workbook in the formulas to the name of the next workbook (spreadsheet) to be used, be sure to choose the "look in" option to look in FORMULAS.  Repeat for the next 58 sheets.

Actually, you could record a macro while doing this the first time to make the first copy of the prepared sheet and edit the filename/path.  Then you could go into the VB Editor and find where the new (replacement phrase) is referenced and change it to another phrase, click [F5] to run it to create a new sheet and make the edits for another filename/path, then change that reference again and press [F5] again and repeat the sequence until you've processed sheets for all the summary sheets you need.