Here's what I would like to accomplish:
If a specific value is entered in cell C2, then the corresponding value it is looked up in a table containing all validation values and is placed in cell H2; otherwise, if cell C2 is empty OR does not match a specific value in C2, the a validation list is available to choose from.
Thanks!
This is actually two separate things.
1) Create a normal VLOOKUP formula in H2. Something like:
=VLOOKUP(C2, Sheet2!A:B, 2, 0)
or
=IFERROR(VLOOKUP(C2, Sheet2!A:B, 2, 0), "")
The point here is to have the lookup occur if/when C2 is used an a lookup value exists.
2) Next, apply a Data Validation setup of:
Allow: List
Source: =IF(LEN(C2)=0, MyList)
-------------------
These operate independently.
If C2 is not empty, the formula will attempt to do the lookup for you and display the result.
If your data validation is setup correctly and C2 is empty or the lookup formula failed to find anything, the "MyList" range of options will appear as a drop down.
NOTE: If you actually USE the drop down to make a selection, then the formula is forever gone from that cell.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Pl upload file with expected result.
This seems to work for the first row. I get the drop down for the data validation list, but all rows below kick out a data validation error and I can't pull the drop down:
Field Type Information
H4
Data Type: List
Restriction:
Value must match one of the listed items.
Blanks will be ignored and are automatically valid.
Any thoughts?
Thanks!
I would have to look at your workbook to see what was wrong.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks