Consolidating data from multiple sheets

[wattzz2000]

I have a estiamte template that uses drop down boxes and Vlookup commands to fill in materials, unit measures, total units, unit cost, and total cost. All of this data across 80 tabs is collected on one tab called Bill of Materials. It works great as long as the drop downs are used; however, I now need to include custom lines where the user entered data can and will change. Also, there is a chance of misspelling and different short hand by the user.
Here is what I want to accomplish:
Have a tab look over the 80 tabs and identify if the custom entered fields contain data and if there is data there then it adds a line to a list resolving the name of the materials, unit measures, total units, unit cost, and total cost. If this material occurs more than once on the 80 sheets then it will add those together on that line.

A typical field looks like this

ColD ColF ColG ColH ColI ColJ
Material Units Unit Meas Total Units Unit Cost Total Cost

The different rows that will contain this info by material category are these:
rows 17:28, rows 36:43, row 45, rows 51:56, row 58, rows 63:68, rows 70:72

I am not sure if I communicated this properly, but any help will be appreciated.

 

[wattzz2000]

Here is another way of stating the same thing that may make it easier to understand.

I am trying to total up materials entered across 80 tabs.
I want a macro or function that will look in certain rows and columns to see if there is data there, if there is I want it to copy the information on to a separate sheet. If there are multiple entries with the same name, then the total quantity and price will total up.

D17:D28 is the name of the materials (this will repeat at times across 80 tabs)
F17:F28 is the Unit Totals (this will need to be added cumulatively if the materials names in the D column are the same)
G17:G28 is the unit measure (EA, SF, etc)
H17:H28 is the total Units (this will need to be added cumulatively if the materials names in the D column are the same)
I17:I28 is the unit cost (this does not need to be returned)
J17:J28 is total cost (this will need to be added cumulatively if the materials names in the D column are the same)