Trying to work out the Best Formula

[Fishyninja]

Hello All. I had a question. I have been tasked with creating an 'Excess Inventory' Spreadsheet. We have multiple work books with the inventory for specific projects and we want a single workbook where all the excess totals are added up for ease of reference. I know how to link workbooks so that's not a problem but in my mind all I keep coming up is for example =(Excess-WkBk1+Excess-WkBk2+ExcessWkBk3....) and do on and so forth. That cant be right.....can it? Also is there a way to auto fill categories, so for example if I am trying to work out the excess amount of A4 folders we have can I establish a method where by inserting 'A4 Folders' into my excess inventory sheet it searches other linked work books to auto fill the data?

[Bob Phillips]

Personally, I would never work with linked workbooks, they always come a cropper eventually. Take all the sheets and put them in one workbook.

Then, explain step by step how you would calculate the excess without a spreadsheet, and we can try and work out what needs to be done.

[Fishyninja]

Hey Bob thanks for getting back to me. Essentially the company I work for will be operating a variety of different projects simultaneously, all with different resource needs. In the past the company has ordered bulk amounts and then stored any left over resources from the product so from now on (or whenever) we'll order as usual but track usage so we can work out what we have in excess so we don't over order. In my mind I am thinking that each project will have its own sheet and to calculate the excesses would be a simple subtraction of 'Quantity Used' from 'Quantity Ordered'. Then from that result linking it into a Master sheet of Sorts. My questions are: 1) Easiest Way to do this 2) Is there a way of auto-filling data for ease of use 3) How to make it idiot proof. Thanks