INDIRECT function and data validation

Hi,

I have a problem with a spreadsheet (attached). The problem (for me) is quite complex and any suggestions would be very welcome. My spreadsheet has three sheets - Costings, Materials, and Validations. In the Costings sheet I have three columns - A, B, C. I am trying to create a complicated dependent list where Column A determines the list in column B, and Column B determine the list in Column C. I have got as far as getting column A to determine the choices in column B using the INDIRECT function with the data ranges being named as the data validation cells for column A:

=INDIRECT(SUBSTITUTE(A3," ",""))

However column C is more difficult as different values in A and B must dictate the list in C. For example if the value in A contains "Offshore Plate" AND "Carbon Steel" the validation list in column C must be the range listed under "OffshorePlate" in the validations sheet. Again this works using the INDIRECT function by combining the values of columns A and B and removing the spaces.

=INDIRECT(SUBSTITUTE(A2&B2," ",""))

However if column A is "SAW Tubular" then Column C will be pointing towards two different validation ranges depending on what is selected in column B. What I want the sheet to do is select the range "Alloysawt", if column B contains anything but "carbon steel" or "carbon steel EU stock". If column B does contain "carbon steel" then I want column C to display the data validation range "Sawt".

Also if column A contains "Structural Hollow Section" I want column C to look up the value in B and then display the data range "Struchs" if any of the lookup values match the contents of the cell in column B. I guess this is another case where 2 conditions need to be met in order to display the correct validation list in column C.

Is this possible? if so could someone please help! I am struggling with this!