Data validation, changing 2 cells at once

exceldemon

New Member
Joined
Jun 30, 2010
Messages
38
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?
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Why can't you just do a VLOOKUP on the item that was selected in A1?
 

exceldemon

New Member
Joined
Jun 30, 2010
Messages
38
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.
 

exceldemon

New Member
Joined
Jun 30, 2010
Messages
38

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Then I'm back to why you can't make a table and put VLOOKUP formulas in C1 and D1.
 

exceldemon

New Member
Joined
Jun 30, 2010
Messages
38

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Jun 30, 2010
Messages
38
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,787
Messages
5,524,870
Members
409,607
Latest member
charleswaynemc

This Week's Hot Topics

Top