yogi_Put Computed results In Appropriate Brown And Green Colored Cells Using Cross Reference Type Checks

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user grebarton said:
Using cross reference type checks to return data in appropriate cells
I am trying to use a spreadsheet to return various parts of information.

For example we work in steel and know that:

Part 1 needs to be made up of 100mm of Steel A, 200mm of Steel B and 2x50mm of steel C,

Part 2 needs to be made up of 200mm of Steel A, 200mm of Steel B and 30mm of Steel D.

I have used a basic VLOOKUP function to return results however have only got the result back where if we input in cell A2 Part 1 it will return the different steel types, with lengths and quantities in a suitable column.

I cannot seem to plan out the best way of doing this.

For example if it would be best have have a list of the different Steel types across the columns (allowing room for lengths and quantities) and the Parts on the rows However I am still unsure how to return the results.

Type Part    Qty            Steel A   Steel B   Steel C

Part 1          10              200 10    200 10    50 20                              

Part 2          10              200 10    200  10   30 10                                          

The actual data to link to will be stored elsewhere within the worksheet.

However we would also want a further basic function built in showing if we wanted 20 x Part 1 in total it would actually take this into account when telling us how many of Steel A to cut to 200mm.

link to user's spreadsheet:

https://docs.google.com/spreadsheet/ccc?key=0AtDIWOWnpWmudDlUOTJ6a3ZwR1gtQkJkRmxfdHgtSlE

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

following is a solution to the problem: