Data validation, changing 2 cells at once

[exceldemon]

I have a data validation setup on Sheet 1 , lets call it "Material Description", cell A1. The list of material descriptions is in Sheet 2, A1 - A10. Also on Sheet 2 is a list of material numbers, B1 - B10. These numbers correspond to the descriptions.

Back to Sheet 1. I have a validation that looks at the "Material Description" nameset from Sheet 2. I am trying to have cell A2 change to match the material number that corresponds to whatever description is selected in A1.

Is this possible?

 

[Scott Huish]

Why can't you just do a VLOOKUP on the item that was selected in A1?

 

[exceldemon]

Why can't you just do a VLOOKUP on the item that was selected in A1?

How do I get the column that is selected in the Validation?

Im also trying to figure out a way to change the validation depending on another field.

=IF(A1="SHEETMETAL",SMTL,PIPE)

Meaning if A1 says Sheetmetal, then the validation list will show sheetmetal description, otherwise show pipe descriptions.

 

[Scott Huish]

OK, I understand your question now:

See here: http://www.contextures.com/xldataval02.html

 

[exceldemon]

OK, I understand your question now:

See here: http://www.contextures.com/xldataval02.html

I dont think thats what I am after.

Here is my example:

Column A
(A1)Material1
(A2)Material2
(A3)Material3
(A4)Material4

Column B
(B1) 53232
(B2) 23553
(B3) 25992
(B4) 44232

I have a list called "Material_Desc" for items in Column A

I have a Validation setup in C1 for "Material_Desc"

When something is chosen in this validation, I would like D1 to show the B column value that corresponds to the validation. Meaning if A3 was chosen, then Material 3 would display in C1 and D1 would display 25992.

 

[Scott Huish]

Then I'm back to why you can't make a table and put VLOOKUP formulas in C1 and D1.

 

[exceldemon]

Then I'm back to why you can't make a table and put VLOOKUP formulas in C1 and D1.

Ok, I got VLOOKUP to work. Now I am trying to figure out how to look at an array on another sheet.

Any ideas?

Here is my formula

=VLOOKUP(B5,SMTL_BLOCK,2,FALSE)

SMTL_BLOCK is (2) columns on another sheet.

 

[Scott Huish]

If you're returning 2 values then your table should be 3 columns the value you are looking up and the 2 values you are going to return.

 

[exceldemon]

Ok, I got it working now.

I am trying to have an IF statement with Design Validation.

=IF(B11="SHEETMETAL",SMTL_DESC,"TEST")

Am I missing something?

 

[Scott Huish]

Are you trying to change the validation based on a selection. If that's the case then my link should help you. If not, can you provide a sample and expected results?