Pull information from different files with mult. sheets

**Master.Ace** · 07-27-2009, 01:48 PM

Hello everybody,

I am sorry but this one is way over my head

so I am asking for help.
I have an x amount of .xls files with the exact same structure. They are all in the same folder but do not have a consistent naming pattern. These are templates containing information. They exist of 13 sheets, individually named. Now I need to create a master list with information from different sheets. These would be something like :
1. Row:Info 1: [file1.xls]sheet1'!$A$5, Info 2: [file1.xls]sheet1!$C$8, Info 3: [file1.xls]sheet2!$E$22, Info 4: [file1.xls]sheet5'!$F$34
2. Row:Info 1: [file2.xls]sheet1'!$A$5, Info 2: [file2.xls]sheet1!$C$8, Info 3: [file2.xls]sheet2!$E$22, Info 4: [file2.xls]sheet5'!$F$34
and so on.
First of all thank you for taking the time to read my post and if you have some spare minutes to help me it would be very vey very much appreciated!!!!
Please let me know if any additional information is needed.

Cheers,

Ace (apperently not in excel

)

**Miraun** · 07-28-2009, 03:45 AM

Hi Ace;

Let me verify that I understand what you're asking:

You have X number of workbooks, each containing 13 sheets within. One of the 13 sheets contains the consolidated information from the 13 sheets.

You're looking to create a single workbook that obtains Z information from that 1 main sheet within each of the X workbooks.

I do not know how to pull a single cell out of the worksheet within each of the X sheets. I recommend the following:

Create a new workbook with X sheets within it. On each Sheet, use Data > Import > Import External Data.

From there, select the workbooks you want, and then it will prompt you for which sheet you want. Choose your header sheet.

I recommend then going into properties and choosing whatever options you desire. I'm personally a fan of:
"Refresh Data on File Open"
"Remove External Data from worksheet before saving"
and "Overwrite Existing cells with new data, clear unused cells"

Hit OK a couple times, and your header sheet will be in there. Then create a header sheet for this file to, to reference whatever specific data you needed out of that pull from the other sheet.

**Master.Ace** · 07-28-2009, 05:31 AM

Originally Posted by Miraun

Hi Ace;

Let me verify that I understand what you're asking:

You have X number of workbooks, each containing 13 sheets within. One of the 13 sheets contains the consolidated information from the 13 sheets.

Hi Miraun,

First of all thank you for you answer. Unfortunaltely it seems that I was not very specific in the explenation. I do have x ammounts of workbooks with the same structure each consisting of 13 sheets. They DO NOT have a "consolidation" sheet. So I need to pull out information like location from the first sheet from a specific cell, Name from the second sheet from a specific cell, Amount from the third sheet from a specific cell and so on. All these data should than be consolidated in on NEW workbook. I hope this clarifies what I meant.
Thank you in advance for your support !!!