Formula?

[jan96ken]

I am trying to create a spreadsheet. Currently we have approx 150 files which are kept in a folder. Each of these files contains 5 worksheets. These five worksheets are the inventories for various departments for our retail stores.

I would like to create a worksheet that would look at each of these files and give me quantities for items. I tried to do a VLOOKUP function but the worksheets containing the inventories are not set up to allow me to do this. (and I would not be allowed to change the appearance of the store inventory sheets)

Is there any formula I can use so that I can pull this data from the worksheets (quantities of items to be exact). I could "hard-key" the data but it's ALOT of data. I could "link" the cells but what do you do then if an item is added to the inventory or the lines are deleted or changed within the worksheets.

Thanks in Advance!!!
Jan

 

[BrianB]

You would do beter to consolidate all the sheets into a single file (or Access table if too many records).

Here is some code that you could adapt
http://www.mrexcel.com/forum/showthread.php?t=140187