Sorry the title doesn't explain what I am trying to do very well... I want to be able to choose a drug type in one column and the route of administration in the next column and then have the dose information for that drug given in that route displayed across the next few cells in the same row. I have attached a simplified demo spreadsheet.

Originally I was using a vlookup command and had both the drug and the route in the same cell, however, I need the route in a separate cell for other use on the spreadsheet.

Also, I was hoping that by using this method I would be able to avoid a mile long drop down menu due to each drug being listed 3 - 5 times (for each route). However, the way I have it set up now the list is just as long, there are just blank spaces. Is there any way to get rid of the spaces in the drop down menu?

Thanx in advance for your help!!

demo.xls

~Dave