Spreadsheet Formula and Validation List Formula

[dparteka]

I am attempting to tweak a spreadsheet formula and a validation list formula that I can’t get to work correctly. I’ve inserted the details of my dilemma within the attached workbook, if you can help I’d be very appreciative... thank you for looking.

 

[rahulshewale1]

HII @dparteka ,

see if is ok ?

=IF(A2="Customer",VLOOKUP(B2,CUSTOMBER,2,FALSE),IFERROR(IF(A2="Supplier",VLOOKUP(B2,VENDOR,2,FALSE),IF(A2="Employee",VLOOKUP(B2,EMP,2,FALSE))),"NOT IN LIST"))


NOTE : red highlighted is name range
Regard
rahul shewale

 

[bosco_yip]

Try,

In C2, enter :

=VLOOKUP(B2,IF(A2="Customer",'TCM Lists'!$A$2:$B$20,IF(A2="Supplier",'TCM Lists'!$C$2:$D$20,'TCM Lists'!$E$2:$F$20)),2,0)

or,

=VLOOKUP(B2,CHOOSE(MATCH(A2,'Other Lists'!$A$1:$A$3,0),'TCM Lists'!$A$2:$B$20,'TCM Lists'!$C$2:$D$20,'TCM Lists'!$E$2:$F$20),2,0)

Regards
Bosco

 

[Naresh Yadav]

Hi,

pls see if this works for you

1:Your formula in C2 was not finished properly owing that you were not getting the correct result, which is rectified now

2: to determine the list in B column i have created the list through name manger which is as follows;

Name Range
Employee E1:E20
Supplier C1:C20
Customer A1:A20

than have used Data validation with indirect function

Go to data validation->Settings->Select List-> and put formula =INDIRECT(A2) in source heading



regards
Naresh

 

[dparteka]

To all... thank you so much, each suggestion is very helpful and each has elevated my knowledge. The Name Manager is a new thing for me, I did some research, very cool feature.

Naresh... question for you on your formula in column C. Open the attachment and you'll see #N/A in C4, this happens if I enter "Supplier" in A4, then enter "12" in B4 and then change to "Customer" in A4. It errors out as it should because there is no "12" in that list. It is possible that this error could happen so as a safety measure I’d like to have a message appear when it does happen, something like what rahul shewale did in his formula "Not on List". Everything I've tried has not worked, probably because of the multiple IF statements which I am a little weak on.

 

[Naresh Yadav]

Hi,

Pls Replace your formula with this one..

=IF(B2="","",IFERROR(IF(A2="Customer",VLOOKUP(B2,'TCM Lists'!$A$2:$B$20,2,0),IF(A2="Supplier",VLOOKUP(B2,'TCM Lists'!$C$2:$D$20,2,0),IF(A2="Employee",VLOOKUP(B2,'TCM Lists'!$E$2:$F$20,2,0)))),"Not in the ""list"))

 

[dparteka]

Naresh... thank you, it works perfectly