I have a workbook which should be used to display data about the current stock in an warehouse. The current data will be imported into the workbook and shoudl then be displayed in one worksheet. I've attached a sample workbook so that you can see what I'm trying to do.
First of all: The ID Generator.
In this example it is possible for one part to be stored in diferent warehouses. There could be 1 copper bolt produced by company1 in store1, while there are 7 in store2. In order to accumulate these positions i figured it would be best to give them individual ids. I think that implementing an individual id genrator would be better than using GUIDs. I've already created the basiscs for the generator, which you can find in the "id" worksheet. The first column of each table is for the (part)id and the second one for the description. Some companys only produce specific products so that the third table consist of a companyID and materialID and then the description.
I've already tried mapping the descriptions to the id by using a combination of VLookUp and Offset, but that doesnt seem to work.
Secondly, after generating the ID the accumulated data should then be displayd in the "data" worksheet. So in the first column should be the 8 copper bolts produced by company1 which are stored in store1 and 2. For that i figured I could use VLookUp to create references rather than copying the data but that would have to be dynamic so that only so many rows are filled as there actually are, rather than filling the whole sheet with the correct formula.