Data Validation List question

[fijian]

Hi,

I have a question regarding the data validation lists. What i have is 4 different lists

Department
Assays
Instrument
Manufacturer

What I want to do is...once a user chooses a department, he gets a list of assays done in the chosen dept. and also a list of instrument used by the dept and a list of manufacturers

I used the examples shown here
http://www.contextures.com/xlDataVal13.html
http://www.contextures.com/xlDataVal02.html

and was able to succeed in creating dependent lists to some extent...I am attaching whatever I have able to accomplish so far, which is able to pull up the assay list based on the dept. ...but am confused as to how to get the Instrument and Manufacturer list at the same time .....please choose 'Chem 2" from the dept list to see what I have managed to do so far

[NBVC]

For the instruments you have named ranged such as Chem2i... so in the data validation you need to include the "i"

Try =INDIRECT(SUBSTITUTE(B4," ","")&"i") for the instruments data validation...


similarly for manufacturers (which I couldn't find in your sheet)

[fijian]

thank you! thank you! thank you!

[NBVC]

No problem

[fijian]

Hi,

I have one more question on the same topic..

My data is arranged in 3 columns (Assay, TEa and % Allowable Difference)....is there a way to extract the TEa values and % Allowable Difference values for the assay that is chosen from the dropdown list?

I am attaching the excel file....hope my question makes sense

Thanks for your help again

[fijian]

could this be possible using the Lookup or the offset function>?....but how do I use it with data validation lists?...pls help