yogi_Pull For Listed Products Row By Row Various Materials From Another Sheet And Multiply By Needed QTY of Products

                                          Google Spreadsheet   Post  #1124
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr 14, 2013
user Cobalt Valkyrie  :(http://productforums.google.com/forum/?zx=yg5pzs6aeocd#!mydiscussions/docs/lDg8PjmoaqY)
How do I use Query on a List then Multiplying by a value
I'm attempting to tie two sets of data in order and then sum them to get a final product.

So far I've had 0% success.

Here is the sample sheet I've put together to help explain my dilema: https://docs.google.com/spreadsheet/ccc?key=0AlrnCdafWg7WdFhTM3lxeDloSkExOTFiRy1mLUEzb1E#gid=1

The 'Materials List' worksheet is a display of data and how many of a set material is needed to make the Part.  

The 'List' Worksheet is how many of these parts are needed to make them into Products and how much of the material per x number of parts.

The 'Totals' worksheet shows the Total amount of each material needed based on the Product needed.  A small dashboard, if you will.

So far on the real sheet I've had limited success.  My approach has been to use the 'query' function to query the 'Materials List' sheet and pull in the materials based on the 'Parts' listed.  This number I can then multiply by the number of parts.

I've tried this two ways:

=query(Importrange("0AlrnCdafWg7WdFhTM3lxeDloSkExOTFiRy1mLUEzb1E"; "'Materials List'!A2:J9"); "select Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10 where Col1='"&C2&"'";0)*E2

and

=query("'Materials List'!A2:J9"; "select Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10 where Col1='"&C2&"'";0)*E2

Neither has had great success. The importrange has had better success than the query within the sheet, but both seem to work only limitedly.

For the dashboard I have tried to simply pull the information in before summing it up.

=TRANSPOSE(query(List!B2:M17; "select Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12 where Col1='"&B2&"'",0))

This has also not worked..

Any assistance/ advice would be most welcome!!

----------------------------------------------------------------------------------------------------------

following is a solution to the problem