Data Validation not working with VLookup

[texas-lab]

Hello,
I am having a problem getting Data Validation to work in conjunction with a VLookup formula. Both items work great separately; however, when I try to get Data Validation to work on VLookup data - invalid condtions are allowed. I have a spreadsheet that is the result of an instrument data dump (worksheet 1). I then want to selectively take out bits of this data so that it can be evaluated in a logical manner (worksheet 2). I want to have Data Validation evaluate 2 data points to ensure that the first value is larger than the second. If I type in the data points everything is fine, but I want the data points to be input automatically by VLookup. I have checked the internet and not been able to find anything that has helped so far. Excel is setup for automatic workbook calculation.

Thanks in advance!

[Don]

Hi and welcome to the forum!

I think you are going to have to use another method to achieve the desired result. Data validation prevents someone from entering the incorrect values into a cell. But, how is it supposed to stop a formula from doing something? Think of data validation as something you use to prevent a user from making a mistake while entering information. If you can include a sample worksheet, we might be able to find a simple solution that does not try to use data validation.

[Sisyphus]

Hi,
Could there be more than one flaw in your logic?
How does VLOOKUP enter a value in a cell?
I presume that VLOOKUP helps you find the value you want and writes it in the cell where it resides. Then you would like Validation to check if it is correct. And then what?

Validation is designed to interact with the human user. If the wrong data is entered it is rejected. Then what? The user is forced to enter correct data. As Don has pointed out you can't give VLOOKUP such a task. Most probably, you will want to omit the data if it is wrong, and therein lies the solution to your problem. You must check the admissibility of the retrieved data before it is entered in the cell, not after.

For this purpose you need a simple IF function: IF( [Condition] , [Value if Condition is True] , [Value if otherwise] ).
It will look somewhat like this:

Code: Select all

= IF( [VLOOKUP] > [Requirement] , 0 , [VLOOKUP] )


in plain English: if the LookedUp value is greater than the required value enter a zero, otherwise enter the LookedUp value. The point is that you actually need the LookedUp value twice and that means that you need to look it up twice. If the database is very big this duplication may take more time than you are willing to wait. In that case you write the LookedUp value to another, adjacent cell and refer twice to that cell. The auxiliary cell isn't required for any other purpose but to speed up calculation. So, you hide the column it is in.

Perhaps this helps. I hope so!
Have a great day.

[texas-lab]

Don and Sisyphus,

Thanks for the replies. I have attached 2 of my test spreadsheets so that you can see what I have done so far. When I try using the =IF statement in combination with the VLOOKUP in M2, it defaults to the Data Validation error message which is a STOP action. This occurs whether there is any data in D2, or the VLOOKUP formula for that cell.

Here is a more detailed explanation of what I am trying to get done, perhaps you can suggest a better way to get the job done. I want the user to enter the Raw ID number in C2 and then have the VLOOKUP search the data dump file (data-test) and fill in the correct Sulfur value. The user will then enter the Processed ID number into K2 and VLOOKUP will plug in the correct sulfur value in M2. The Processed Sulfur value will always be lower than the Raw Sulfur value, unless there was a problem with one of the samples. In which case, the Processed data will not be entered into the spreadsheet (Data Validation-test) and the user will know that there is a problem and will have to remake the samples and run them again. Hope that between this explanation and the attached test files you will be able to help me out.

Thanks in advance for your efforts!

[Sisyphus]

Hi,

It's simple. It is's not, make it so!
1. You can't have the data validation in M2 (because M2 isn't filled by a person).
2. You need to check the data according to the rules in your data validation.
3. Therefore, the formula filling M2 must take over the job of data validation.
Here is your formula blueprint:

=IF(VLOOKUP(K2, [Reference Range], FALSE) <= D2,"ERROR", VLOOKUP(K2, [Reference Range], FALSE))

Remove the data validation from M2.
Enter the above formula there after replacing the square brackets with the LookUp range.
Now, when the imported value isn't bigger than D2 that value will not be shown (entered). Instead, the word "ERROR" will appear in the cell. You can make sure the user will notice it by using conditional formatting to make the font bold and red and highlight the cell yellow once it contains the word "ERROR". In the above formula you can replace the word "ERROR" with any word or text that both meet your requirement and fit the cell.

One more problem you won't have to carry into 2012.
Have a great day!