Using Data Validation with a vlookup formula

[Tanya]

Excel 2007

Working with 2 workbooks. In RESULTS.xlsx I have a Data Validation set
up on a Column using a named range (Data) on a separate worksheet. The
data validation works fine if I am manually entering information. The
problem/question comes when I try to use a vlookup formula to pull
data from SOURCE.xlsx, the data validation does not seem to 'catch'
the 'bad' data.

I'm thinking I may have to write the validation into the formula or
try something with conditional formatting. Either way, I'm at a loss.

Any assistance would be appreciated.

Thanks!
Tanya

 

[JLatham]

Can we see one of your VLOOKUP() formulas? Should work just fine with or
without the second workbook being opened.

In a test set up here, I set up a list in my Results book on Sheet3 and
named it. Then I set up data validation using that named range in A2 on
Sheet1.

In my source book, I set up a table on its Sheet1 from A2 through C9, with
A2:A9 containing the same contents as in my named range in the Results book.

With both books open I set up this formula in B2 of Sheet1 in the Results
Book:
=VLOOKUP(A2,[Book1_Source.xls]Sheet1!$A$2:$C$9,2,FALSE)
and that worked fine; change the selection in A2 and I get proper return
from the table in the other workbook, then I closed the other (Source) book
and Excel changed the formula properly to:
=VLOOKUP(A2,'C:\Documents and Settings\jlatham\My
Documents\[Book1_Source.xls]Sheet1'!$A$2:$C$9,2,FALSE)
and it all still worked for me; make a change in A2 and B2 fills out
properly via the VLOOKUP().

 

[Tanya]

Thank you for responding. The vlookup I was using is working...that's
not the issue. The problem came when trying to make sure the data
return was valid, i.e. in the list used for the data validation. For
example, if the list contained the following; apple, orange, cherry;
and the vlookup returned banana, no data validation warning appeared.
Now, if I manually typed in banana, I would get the warning message.

Can we see one of your VLOOKUP() formulas?  Should work just fine with or
without the second workbook being opened.

In a test set up here, I set up a list in my Results book on Sheet3 and
named it.  Then I set up data validation using that named range in A2 on
Sheet1.  

In my source book, I set up a table on its Sheet1 from A2 through C9, with
A2:A9 containing the same contents as in my named range in the Results book.

With both books open I set up this formula in B2 of Sheet1 in the Results
Book:
=VLOOKUP(A2,[Book1_Source.xls]Sheet1!$A$2:$C$9,2,FALSE)
and that worked fine; change the selection in A2 and I get proper return
from the table in the other workbook, then I closed the other (Source) book
and Excel changed the formula properly to:
=VLOOKUP(A2,'C:\Documents and Settings\jlatham\My
Documents\[Book1_Source.xls]Sheet1'!$A$2:$C$9,2,FALSE)
and it all still worked for me; make a change in A2 and B2 fills out
properly via the VLOOKUP().

Excel 2007

Working with 2 workbooks. In RESULTS.xlsx I have a Data Validation set
up on a Column using a named range (Data) on a separate worksheet. The
data validation works fine if I am manually entering information.  The
problem/question comes when I try to use a vlookup formula to pull
data from SOURCE.xlsx, the data validation does not seem to 'catch'
the 'bad' data.

I'm thinking I may have to write the validation into the formula or
try something with conditional formatting.  Either way, I'm at a loss..

Any assistance would be appreciated.

Thanks!
Tanya- Hide quoted text -

- Show quoted text -

 

[JLatham]

Well, hard to explain, and actually a little hard to set up. I had to first
enter the VLOOKUP() formula into a cell, and then set Data Validation for
that cell before I could even begin. But I did that, and you are correct, no
alert given when the vlookup() returns a value not in your Data Validation
list. Obviously, Excel is circumventing the test for error when a formula is
used to obtain the value.

Then when you type the same illegal value that was returned by the
VLOOKUP(), you do get the alert. BUT you have to also realize that the
instant that you manually enter banana into that cell, the VLOOKUP() formula
is gone!! Forever - or until someone types the formula back into the cell,
which data validation is going to refuse to accept. A cell can hold a value
or a formula, but not both at the same time.

Since your formula should only be returning valid values, I'm not sure I see
the problem. Of course you may have a purpose in allowing the VLOOKUP() to
return an invalid value. Also, generally, cells with formulas in them should
be LOCKED (via Format --> Cells --> [Protection] tab) and then the sheet
should be protected to enable that lock so that users cannot overtype your
formulas with values.

Thank you for responding. The vlookup I was using is working...that's
not the issue. The problem came when trying to make sure the data
return was valid, i.e. in the list used for the data validation. For
example, if the list contained the following; apple, orange, cherry;
and the vlookup returned banana, no data validation warning appeared.
Now, if I manually typed in banana, I would get the warning message.

Can we see one of your VLOOKUP() formulas? Should work just fine with or
without the second workbook being opened.

In a test set up here, I set up a list in my Results book on Sheet3 and
named it. Then I set up data validation using that named range in A2 on
Sheet1.

In my source book, I set up a table on its Sheet1 from A2 through C9, with
A2:A9 containing the same contents as in my named range in the Results book.

With both books open I set up this formula in B2 of Sheet1 in the Results
Book:
=VLOOKUP(A2,[Book1_Source.xls]Sheet1!$A$2:$C$9,2,FALSE)
and that worked fine; change the selection in A2 and I get proper return
from the table in the other workbook, then I closed the other (Source) book
and Excel changed the formula properly to:
=VLOOKUP(A2,'C:\Documents and Settings\jlatham\My
Documents\[Book1_Source.xls]Sheet1'!$A$2:$C$9,2,FALSE)
and it all still worked for me; make a change in A2 and B2 fills out
properly via the VLOOKUP().

Excel 2007

Working with 2 workbooks. In RESULTS.xlsx I have a Data Validation set
up on a Column using a named range (Data) on a separate worksheet. The
data validation works fine if I am manually entering information. The
problem/question comes when I try to use a vlookup formula to pull
data from SOURCE.xlsx, the data validation does not seem to 'catch'
the 'bad' data.

I'm thinking I may have to write the validation into the formula or
try something with conditional formatting. Either way, I'm at a loss..

Any assistance would be appreciated.

Thanks!
Tanya- Hide quoted text -

- Show quoted text -