Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLookUp and DropDown List in the same cell
Hi everyone,
I know that this is possible but I'm not sure how to accomplish this. In column E, I have a list of zip code. In column I, I have the "Priority" category. Priority will be based on the zip code in column E. This will be accomplished by VLookUp formula, where I will have a separate sheet store all the zip codes and the priorities (ie. Priority #1, Priority #2 and so on). The challenge part is that column I "Priority" column needs to be able to use as a Drop Down List as well, in case that the VLookUp formula needs to be overridden for someone who doesn't follow the zip code Vlookup formula. With Drop Down List setting in Data Validation, I know that it won't allow and formula, which mean only one thing and not the other. I know that this can be accomplished with VB, but I'm not sure how. Please help! Also, could you tell me where to put the code as well? Thank you, Neon520 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLookUp and DropDown List in the same cell
You should not need VBA to do this. If you put your formula in before
applying the validation you should be all set. The formula can evaluate to anything you want and does not have to match the validation list output. If you already have the validation set and need to enter the formula (or edit it) turn off the error alert (or set it to Style:Information) and make your formula changes. After you enter the formula you can turn the error alert back on (or reset the Style). -- If this helps, please remember to click yes. "Neon520" wrote: Hi everyone, I know that this is possible but I'm not sure how to accomplish this. In column E, I have a list of zip code. In column I, I have the "Priority" category. Priority will be based on the zip code in column E. This will be accomplished by VLookUp formula, where I will have a separate sheet store all the zip codes and the priorities (ie. Priority #1, Priority #2 and so on). The challenge part is that column I "Priority" column needs to be able to use as a Drop Down List as well, in case that the VLookUp formula needs to be overridden for someone who doesn't follow the zip code Vlookup formula. With Drop Down List setting in Data Validation, I know that it won't allow and formula, which mean only one thing and not the other. I know that this can be accomplished with VB, but I'm not sure how. Please help! Also, could you tell me where to put the code as well? Thank you, Neon520 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLookUp and DropDown List in the same cell
Hi Paul,
Thank you for your quick tip on how to get this done! I'm testing your suggestion right now, all is well until I "explicitly" change the Priority setting of the VLookUp formula with the Drop Down List. The formula has been overwritten by the Drop Down List and won't evaluate another value if the zip code change for whatever reason. The formula just isn't there any more. I'd like to have the formula there in case the zip code is changed for whatever reason. Is this possible to do this without VB? I don't mind VB, but since I'm not quite familiar with it, I'd rather use non-VB solution whenever possible. Thank you, Ny "Paul C" wrote: You should not need VBA to do this. If you put your formula in before applying the validation you should be all set. The formula can evaluate to anything you want and does not have to match the validation list output. If you already have the validation set and need to enter the formula (or edit it) turn off the error alert (or set it to Style:Information) and make your formula changes. After you enter the formula you can turn the error alert back on (or reset the Style). -- If this helps, please remember to click yes. "Neon520" wrote: Hi everyone, I know that this is possible but I'm not sure how to accomplish this. In column E, I have a list of zip code. In column I, I have the "Priority" category. Priority will be based on the zip code in column E. This will be accomplished by VLookUp formula, where I will have a separate sheet store all the zip codes and the priorities (ie. Priority #1, Priority #2 and so on). The challenge part is that column I "Priority" column needs to be able to use as a Drop Down List as well, in case that the VLookUp formula needs to be overridden for someone who doesn't follow the zip code Vlookup formula. With Drop Down List setting in Data Validation, I know that it won't allow and formula, which mean only one thing and not the other. I know that this can be accomplished with VB, but I'm not sure how. Please help! Also, could you tell me where to put the code as well? Thank you, Neon520 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookUp and DropDown List in the same cell | Excel Worksheet Functions | |||
vlookup with dropdown list | Excel Discussion (Misc queries) | |||
vlookup with dropdown list | Excel Discussion (Misc queries) | |||
HOW DO I CREATE A DROPDOWN PICK LIST FROM A VLOOKUP with multiple | Excel Worksheet Functions | |||
VLOOKUP specific data for month required via dropdown list | Excel Worksheet Functions |