CONCATENATE two cells to generate the validation source

[nancysheen]

Must be 2007 compatible.
I have a table with more than 50 columns and over 3,000 rows. I need users to update multiple columns of data with valid values. Each cells needs to offer different dropdowns based on their values.
Let me try to explain:
NameRanges:
USA_Prefix (all of the prefixes allowed for any widgets sold in USA)

• ABC
• GHI
• WW
• MN

Canada_Prefix (all of the prefixes allowed for any widgets sold in Canada)

• DEF
• GHI
• WW
• BFF
  
  |----------------------------------------------------------------|
  |Column.........A.........B..................C............................|
  |..................ID.........Prefix.........Country...................|
  |Row.1.........252.........ABC.........USA............................|
  |Row.2.........333.........BFF..........Canada.......................|
  |Row.3.........861.........MN...........USA...........................|
  |----------------------------------------------------------------|
  
  
  I want to be able to tell excel to use
  

   [CONCATENATE (C1,”_”,$B$1)]

  to get [USA_Prefix] which then would provide the dropdown options to be used by dynamically setting the namerange for the validation source for any cells in column B. I have multiple columns that have this type of validation required, not just one.
  Is this possible?
  Thanks,
  Nancy

 

[Badnames]

I manage a model that I use concatenate in to setup my dropdowns for various products. You have to define all various combinations from concatenated cells names elsewhere and then refer to them.

So for example I have USA_ProductA and i know Product A has 4 options.

I am going to save product A's information on a tab 'lookups'.

A1 = Product
A2 = Option1
A3 = Option2
A4 = Option3
A5 = Option4

Define/Name A2:A5 as 'USA_ProductA'

Now in my input spreadsheet I'll have a cell, lets use X2, that calculates the concatenate "USA_ProductA"

Now I want my dropdown, cell B2, to reference anything in list USA_ProductA. Go to Data Validation, select list and for source set it to =indirect(x2).

Now as long as your lists are preset names, when your concatenate (X2) calcs, B2 will change to that list depending on the concatenate.


Does this make sense?