VLookup Substitute

I am trying to create an estimating workbook for my personal use which contains approximately 15 worksheets & 5 master sheets. Each of the worksheets is broken down into 5 sections (labor, equipment, materials, etc) which are linked using the = to one of five master sheets for Labor, equipment, etc. I am trying to determine the best way to get a total quantity on the Master sheets for each entry made on the worksheets. Since every entry that i enter on the Master sheet may not be used on an individual work sheet I tried to use the Vlookup function to search on each of the 15 individual sheets in the appropriate range of cells or section of the worksheet to see if an entry like equipment operator (EO) on the Labor master sheet is on a particular sheet & return the quantity from a specific column related to that entry. I then used the Vlookup function again to lookup a second column for the same item, equipment operator (EO), on the same sheet and return the number of days the equipment operator(s) were required. I multiplied these two results to get the total EO required for a sheet & added all the same vlookup functions from the other 14 worksheets. As you might have guessed the formula that I worte to lookup 2 entries, multiple them & add similar entries from 14 other worksheets was rather long. The vlookup function worked well on master page items where there was a specific entry on each of the 15 worksheet pages. However if one worksheet did not have an entry that was being looked up it returns a NA message & my master sheet formula for that specific entry becomes NA even though there may be numerous entries for this item on many of the other 14 work sheets. Does anyone know a simpler method or a way I can correct this problem so I can get an accurate total from all 15 worksheets for each item on my master sheets. As you can tell I am not a programmer just someone trying to expnd my use of excel. Thanks

As soon as you break your data into many sheets you will continously run into problems of analysis that spans several sheets. I keep my data all on one sheet and then have one column that indicates the type of data it is. Then I use filters, Pivot table , sumifs to analize and look at my data.

For example lets say you have 3 costs/expense sheet one called NorthAmerica anther Europe and Another Middle East. I would keep all on same sheet and have one column called "Region" then put the NorthAmerica, Europe, Middle East in that column . Then when I only want to look at a specific Region I filter by region. When I want to Sum by a region I use SumIf's etc etc.

In short if the data structure looks the same then I wouldn't break them into several sheets... I would just get good with the "data" tools provided i.e. pivot tables , Sumif, Filter , Advanced Filter ..