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?
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
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,958
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,958
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,958
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,477
Messages
5,831,909
Members
430,090
Latest member
bjonesh2o

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top