Getting porduct code against porduct name

[pragnesh_50]

Hi,


Sheet1

I have Products coded like 1234, 9809, 7987(Code of prod.).....etc in column A,

Products name like...icecrem, chocolate, fruits(names of prod) ....in Column B.


I want respective "product code" as output when i put "product Name" in other sheets


Like if i put Fruit in 2nd sheet than code 7987 come automatically...

if i put Icecrem than code 1234 come automatically.


All products have their unique code in sheet1.


I have around 2000 unique coded with respective product name....


Regards,

Pragnesh

 

[Luke M]

Assuming you're using B2 on sheet2:

=INDEX(Sheet1!A:A,MATCH(B2,Sheet1!B:B,0))

 

[pragnesh_50]

Hi,


it it working. I have followng additional two question now:


1.I want to protect column A of sheet B where i get product coded automatically so that it can't be changed and no one can see formula in this column. (For Excel 2003)


2.I have another book where in column A i have various Characteristics of products like Weight of Products, Number in Each Pack, etc...


Now i want to predefine the Format of values of this characteristics like for "Weight" value should be in "G", "ML", "CL" e.g 210G, 500G, 200ML etc.


when i put different format like 210 GRAMS, 1'S, 2'S it should not be inserted for the character type WEIGTH OF THE PRODUCTS..


I have around 200 charter type.


Regards,

Pragnesh

 

[Luke M]

Select column A. Format cells, protection, check the "hidden" box.

Select any cells the user needs to be able to change on the sheet, and under format cells, protection, uncheck the "locked" box.

Tools - protect - protect sheet.

 

[pragnesh_50]

Hi Luke M u are so quick to answer that i am editing my Note and you have answered my 1st question. Thanks for grate help.


Also solve my 2nd one....Lol

 

[Luke M]

You're welcome Pragnesh. I'm afraid I'm having trouble understanding your 2nd question.

Could you share a bigger example of what you have, and what you want it to look like?

 

[pragnesh_50]

Hi Luke M,


CHARACTER TYPE -----------VALUES (FORMAT)

IF WITH CUTLERY ---WITH CUTLERY CLAIM/WITHOUT CUTLERY CLAIM

IF WITH FRESH PACKED CLAIM --WITH FRESH PACKED CLAIM/WITHOUT FRESH PACKED CLAIM

LEVEL OF FAT CLAIM----- 23%, 60%,12% ETC

MASS - WEIGHT GROSS---- 125G, 130G, 1500G

NUMBER IN EACH PACK - ACTUAL 1'S, 5'S, 16'S

TOTAL VOLUME 300GRAMS, 900GRAMS .


i want to make clear data base so that all values are correctly pasted against respective character type like....if i try to past 20OG against LEVEL OF FAT CALIM it should not be accepted against that Character type..


if you not clear check https://docs.google.com/spreadsheet/ccc?key=0Apmikxh9sFKcdGpfcWtGMW9aazlpOVBYS0s1d3ZtdVE


Regards,

pragnesh


I want to prepare protected data base so that wrong value can not be filled for respective char types.


Regards,

Pragnesh

 

[Luke M]

It looks like you'll want to setup some type of custom data validation. I'm not sure what all the rules you have are, but from a few examples:

=IF(OR($A2={8,1339,4}),ISNUMBER(E2),IF(OR($A2={28947,1335}),ISTEXT(E2),E2<1)


Intent is if A2 is one of the 1st 3 code, E2 needs to be a number. If A2 is one of the 2 code states, it needs to be text. Otherwise, E2 needs to be less than 1 (a percentage).