INDIRECT function and data validation

**penguintar** · 03-23-2011, 06:37 AM

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!

**Roger Govier** · 03-23-2011, 06:55 AM

Hi

Take a look at the tutorial I wrote at
http://www.contextures.com/xlDataVal15.html

There is also a downloadable example.
It may provide you with some ideas of how you can arrange your validation.

**Charlie_Howell** · 03-23-2011, 04:02 PM

Your lists have to be set up with great care. Every choice that can be made through Data validation must have a "SUB LIST" Gander at this sheet and look at the lists and Named ranges. Let us know if this makes sense to you. You are obviously not a beginner user so i'm sure you'll get easy enough.