How-to? IF(Calculation Result<>#) Show Dropdown List (Data Validation)

Greetings to all here on the forum;

I have a spreadsheet that uses vlookup to determine the density of multiple materials based on a table that I create as the data admin, however some materials have several possible densities. Thus my goal is something to the effect of a cell that returns a vlookup value unless the material is one of the ones that has multiple possible density values, in which case I want it to show a dropdown list with the allowable densities.

Essentially its an "if normal material, return tabulated density, if not, provide a data validation style drop down list".

You may see attached - this is a simplified representation without any of my macros, hence why it is extremely basic in nature.

Many many thanks in advance for your assistance!

Thanks

Looks like it should work, but having a problem implementing it

Ok, first of all I added a named range called "drop" that had the various densities that "C" could be.

I also added a message box after the If c.Offset(0, -1) = "C" Then
to let me debug it better, problem is I cannot get this event to trigger. I have confirmed that every time I change the worksheet the code is running, but this particular condition is never true. I have tried entering "C" in column A both via validation and via manually typing, both upper and lower case....

Any ideas?

Thanks again

Originally Posted by martindwilson

nope!
mind you i had to save the workbook with the code in then close reopen to get it to work.

This was the ticket - works great now MANY MANY THANKS... I closed then reopened and it worked, I was sort of surprised that this was required, should I not be surprised?

PS. I am running Excel 2003

Excellent, I really appreciate the help folks, my spreadsheet is coming out great!