Naming a column B range based on Col A value

[aravindhan_3]

Hi,

I have the data in column A - Country & column B the item sold in that country, I want to name the Range of items in column B with the name col A

eg

Country Item
China Clipboards these 2 should be named as China
China Pens
India Binders These 3 should be names as India
India Pencils
India Pens
Malaysia Binders These 4 should be names as malayisa
Malaysia Clipboards
Malaysia Pencils
Malaysia Pens


background:-
I am trying to create a dashboard based on this, if I select a country, the respective items should be in dropdown list in another cell. if this is was less number of rows, I would have created manually & used indirect function in my data validation. the rows are more than 100000, and the data will keep changing..

can anyone help me with the macro to create names automatically?

cross post: http://www.mrexcel.com/forum/excel-questions/925568-naming-column-b-range-based-col-value.html
Thanks for your help
Regards
Arvind

[SamT]

What Type Control is the Drop down list?
How do you "if I select a country?"
Does the list of Countries change?
Is Column "A" Sorted alphabetically?

[aravindhan_3]

Hi Sam,



What Type Control is the Drop down list? - Data validation list if I name them, then I can use indirect function to in sheet 2, I will create a usual list with country names, and then in col B I will get list of items.
How do you "if I select a country?" - Data validation list
Does the list of Countries change? = I might add more countries in the rows, but I will be adding the as well in the validation list
Is Column "A" Sorted alphabetically? - Yes


hope this helps

[SamT]

I had a similar situation once, but my lists went 6 deep, covered 2 sheets with multiple lists in each column, and the data from the lists was 3 columns wide.

I found the best way was to organize the lists was like



Countries



China
India
Malaysia


China

clipboards
paper
Ink



India

Pens
Erasers
Staples


Malaysia







If you can do this then put this code in the ThisWorkbook code page
Option Explicit

Sub MakeNewNamedRanges()
Dim Headers As Range
Dim Cel As Range
Dim Nme As Name
Dim strRefersTo As String

'Clear all Names in Sheet
For Each Nme In ActiveSheet.Names
Nme.Delete
Next

Set Headers = Range(Cells(1, 1), Cells(1, Columns.Count).End(xlToLeft))
For Each Cel In Headers
If Cel.Value <> "" Then
strRefersTo = "=" & ActiveSheet.Name & "!" & Range(Cel.Offset(1), Cel.End(xlDown)).Address
Names.Add Name:=Replace(Cel, " ", "_"), RefersTo:=strRefersTo
End If
Next Cel
End Sub

And run it whenever you make a change to the sheet