Picking Data from different Data Sheets

[cherry2000]

I have a Data sheet for each LV motor, with various data viz. HP, No of poles, FLA etc.

Now, I want to populate a single worksheet with this data against the motor Tag. Is it possible to pick up the data from multiple Data sheets, located in the same folder?

 

[MintJulep]

Yes.

 

[cherry2000]

Next question..(obvious)!! How? If you the Macro code, do post it, and I will suitably customize it. Thanx.

 

[MintJulep]

That would be highly dependent upon the structure and arrangement of the various files.

If, by chance the individual motor file names contain the "tag", or a logical relation to the "tag" you could probably do it with native functions.

If not, are all the files consistent in their layout?

 

[cherry2000]

I was thinking of simple Vlookup, with the path of the file for search purpose..but I was not succesful...the layout is consistent, but unortunately because of insertion of rows, the cell nos may not be identical for the same piece of info..say speed/HPrating/No of poles etc....

 

[xnuke]

Since you mentionded Vlookup, I'm guessing you are using Excel for this. My opinion is that spreadsheets are not meant for data storage - databases are. This task would have been trivial in a relational database. I think you should consider migrating your data sheet information into a database.

xnuke
"Live and act within the limit of your knowledge and keep expanding it to the limit of your life." Ayn Rand, Atlas Shrugged.
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[cherry2000]

Hi Xnuke..I agree with you..but there are some restrictions in the ways that we employ methods is a normal EPC project..Our motor data sheets are all in Excel. Each one has a seperate document number which is closely linked with the motor Tag no. If anybody can help in getting Vlookup working with range reference containing the path of the file, it will be awesome.!

 

[MintJulep]

Parse out, build up, or use a lookup to get the file name for the motor of interest.

Use that string in the INDIRECT() function to build the path to the file and a range within the file and plunk that all inside a VLOOKUP()