Adding validation in excel according to another column from another sheet

[javadeb]

I have a excel sheet called data with 3 columns: id, name
I have another sheet called list with 3 columns: id, name
I have made a data validation on column name in data sheet with this

> formula: =list!A$2:$A$37000

its working fine and gives me a drop down list from my names in list and another formula for column id in my data sheet

> formula: IFERROR(VLOOKUP(B${i},list!A1:B10000,2, FALSE),"")

problem is that if some rows in list sheet have same names, if I change value in drop down list in data sheet, id wont change. What should I change to make it work?

[example file][1]


[1]: Deleted

 

[javadeb]

first link got deleted this is the right one

 

[Fluff]

I cannot get to that site as I get a warning "This webpage is a known malicious webpage." So I would suggest that you use a more respected site such as OneDrive, GoogleDrive, DropBox.

 

[javadeb]

I cannot get to that site as I get a warning "This webpage is a known malicious webpage." So I would suggest that you use a more respected site such as OneDrive, GoogleDrive, DropBox.

Sorry I have uploaded it to dropbox

 

[Fluff]

How y=would you know which is the right value to return for AB?

 

[javadeb]

How y=would you know which is the right value to return for AB?

I will add another cloumn like store to know which is wich

 

[Fluff]

In that case can you post some sample data with the store.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[javadeb]

In that case can you post some sample data with the store.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

test.xlsx
	A	B	C
1	id	name	store
2	1	AB	mr jack
3
4
5
6
data

Cell Formulas
Range	Formula
A2	A2	=IFERROR(VLOOKUP(B2,list!A1:C10000,2, FALSE),"")
C2	C2	=IFERROR(VLOOKUP(B2,list!A1:C10000,3, FALSE),"")

Cells with Data Validation
Cell	Allow	Criteria
B2	List	=list!A$2:$A$37000

test.xlsx
	A	B	C
1	name	id	store
2	AB	1	mr jack
3	AB	2	padd
4	VX	3	mr jack
5	WE	4	mr jack
6
list

 

[Fluff]

Using Vlookup like that you are never going to get padd as the store. You would need to use a dropdown in col C to select the store.

 

[javadeb]

Using Vlookup like that you are never going to get padd as the store. You would need to use a dropdown in col C to select the store.

How can I do that to if I select another store id should change?
Could you please help me!