Data Validation List question

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

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)

No problem

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

Try this formula in I4:

Please Login or Register  to view this content.

and this formula in I6:

Please Login or Register  to view this content.

Hi,

Thanks for replying

I tried the solution you provided but it didn't work. Gave me a "Value not available error".

If you don't mind, could you please take a second look at it....I would really appreciate your help.

I am attaching the current excel file just in case I might have changed something since the last time I posted.

Thanks again.

After you choose a "Department", the other lists refresh but the previously selected item remains. You have to choose an item from the list....

The item that was there was not actually in the "Chem4" Department list.

Try selecting Department, then select a Assay, etc...

The formula works on the sheet you sent...you just have to make sure to pick from the lists when you change department.

Originally Posted by fijian

Hi,

For some reason, my formulas don't work as expected i.e. the cells show the formulas that was entered...am attaching an image to show what I mean

Maybe that's why the formulas are not working for me..would you have any idea why it would be so

Thanks again for all your help

I noticed that with all your attachments and thought you purposely had done that so that I would know where your formulas are....

You have Formulas option on in the Tools|Options >>View Tab. Uncheck it to view results instead.