Saturday, December 17, 2011

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: