Dear Forum members,
I want to populate the value of a specific cell based on the choices made from two other dependent validation lists.
I am attaching a workbook that explains what I need to do.
I consider myself a novice Excel user. Please explain in very simple terms. Assume that I know nothing
Autopopulate the specific customer number from column C if individual or from Column F based on the value in B40.
you can create a lookup
=IF ( A40 = "Individual", LOOKUP(B40,{"Private individual","Prospect custmer","wesbank"},{"01",99,14}), LOOKUP(B40,{"Private individual","Prospect custmer","wesbank"},{??,??,??})
so that will do the lookup
BUT not sure what you wanted to lookup in F40 ?
Last edited by etaf; 02-15-2014 at 07:39 AM.
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Hi Louisa
An alternative way of doing it making use of the named ranges you had already created plus 1 I added 'customer' which is just $A$6 and $A$7 you could also use this for the data validation of A40
Regards
Please Login or Register to view this content.
Ian
Hi Wayne, thank you. I tried the formula, but it gave an error message, which I am attaching.excelerror.JPG
The idea is to do the following:
a) In cell A40 select individual or juristic based on customer type in column A.
b) Cell B40 is a dependent validation list. If I select individual, I only see the values in column B. If I select juristic, I only see the values in column E.
c) If I select individual, and from the dependent drop down select Westbank, then cell C40 should populate with the number of Westbank as per column C, if I select prospect customer, then cell C40 should populate with the number prospect customer as per column C.
d) However, if I select juristic in cell A40, then the drop down chooses in cell B40 is limited to the juristic choices in column E. Should I select public company from the dependent dropdown, cell C40 should populate with the number for the public company based on Column F.
Louisa
Hi Ian,
Thanks for the suggestion.
I tried it. but received an #name error.
Louisa
use, in C40
=IF(A40="individual ", LOOKUP(B40,{"Private individual","Prospect custmer","wesbank"},{"01",99,14}),VLOOKUP(B40,JurNrLookup,2,FALSE))
NOTE in individual you have a space after the word
Hallo Wayne,
Thank you. I tried the formula, but got an error message again. Then I realized that I need to replace all the , in the formula with ;. The error message disappeared.
The formula works like a charm when I select juristic, the dependent data validation lists works correctly and upon selecting the values from the list, cell C40 is populated with the correct number.
However, when I select individual in Cell A40, the dependent validation list opens up correctly, but when I select any of the values from the list, I receive a #N/A.
I am sure there must be something very simple that I am not seeing.
I am attaching a second version of the workbook with the formula in it so that you can see what I mean.
Louisa
its the space as mentioned at the end of individual
on the first sheet the validation looked for
individual with a space at the end
try this
=IF(A40="individual", LOOKUP(B40,{"Private individual";"Prospect customer";"Wesbank"},{"01";99;14}),VLOOKUP(B40,JurNrLookup,2,FALSE))
with the changes needed for your version , to ;
Hi Wayne, this worked perfectly. Much appreciated.
your welcome
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks