Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Data Validation with a vlookup formula
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Data Validation with a vlookup formula
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" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Data Validation with a vlookup formula
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. On Jun 1, 12:16*pm, JLatham wrote: 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" wrote: 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 - |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Data Validation with a vlookup formula
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. "Tanya" wrote: 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. On Jun 1, 12:16 pm, JLatham wrote: 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" wrote: 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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation-VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup in data validation??? | Excel Worksheet Functions | |||
Data Validation vs VLOOKUP - Linking to data in a seperate file | Excel Worksheet Functions | |||
IF, VLOOKUP & DATA VALIDATION TOGETHER | Excel Worksheet Functions | |||
data validation using vlookup | Excel Worksheet Functions |