Read info from a seperate spreadsheet

[WelshyWizard]

Hi all,

I'm looking for a quick, clean and easy way to read certain information from several Excel spreadsheets and amalgamate them into one sheet.

Can anyone give me any suggestions? I can think of a few ways but they're all a bit messy.

The information is in the same cells on all spreadsheets.

Thanks.

Today is the tomorrow you worried about yesterday - and all is well.....

 

[xlbo]

depends on how much info and in what layout

This is a bit like asking what the best way to loop is - depends on your specific situation.

More specifics = better answer......

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[WelshyWizard]

Yes, sorry Geoff, I was in a bit of a rush with that one!
Basically I have the following information....

On Workbook1 I have 2 sheets (sheet1 and sheet2). On sheet one I have a list of contract numbers, a sample of which is listed below:
899001
901001
920121
922001 etc...

Sheet2 is blank and is where I want my information to go.

For most (not all) contract numbers listed on sheet1, there is a corresponding workbook which will be called 899001.xls for example.

In 899001.xls there are a list of numbers in column D that I would like to place on sheet2 in Workbook1. These numbers will always be in column D and will always appear on the same rows (but not every row in column D). The same applies for 920121.xls, 922001.xls etc...

Once the values from 899001 have been taken, I would then like the values of the next contract listed on sheet1 to be appended onto the next available row on sheet2... And so on...

I hope this is clear... It's quite difficult to find the right way of explaining what I'm tring to do.

Thanks.


Today is the tomorrow you worried about yesterday - and all is well.....

 

[xlbo]

ok - so you need to loop through a list of data in sheet1 and open a file that corresponds with the string in the cell.

The file may or may not exist

You want to pick up data in these files and append it to Sheet2 of the main workbook

Coupla questions:

Does the no. of rows in each file change ?
Can there be blank rows between rows of data in these target workbooks?
Are all the target files in the same folder ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[WelshyWizard]

Geoff,

In answer to your questions:

No, the number of rows is always the same.. The data starts in row 12 and always ends in row 80.

Yes, there will be blank rows between rows.

Yes, all files will be in the same target folder.

Thanks for your time.

Today is the tomorrow you worried about yesterday - and all is well.....