Create/Update Name Range Dynamic

[gdisalvo]

I will try to explain. First to let everyone know, I search the WEB for an answer and nothing seems close.

I have a SHEET. First couple of columns is data on Left side and right side is drop downs to drive different answers.

It is working but each day I get different data and would like to have the name ranges dynamic or a VBA to execute and update the ranges.

Data Below
COL A Holds key values
COL B different names

A Joey B
A Anthony R
A Albert P
A Brandon B
B Lucas D
B Mike N.
B Chris D.
C Joey Z
C Anthony Z
C Albert Z
C Brandon Z
D Lucas Z
D Mike Z.
D Chris Z.

I want a range for all COL A value A then all value B and so on. Each day the range of A, B, C, D Increase or Decrease. I just want the drop downs to hold the correct names based on the values of the range of A, B, C, D.

can someone show me a VBA to create these four ranges each day. Or if a formula that can be used to determine the first row of A to the last row of A then keep that range of names from column B for the dropdown indirect?

 

[C Moore]

This could be done with the name manager and offset function. Key value B for example would be keyB_list =offset($A$1,match("B",$A:$A,0)-1,1,countif($A$A,"B"))

 

[gdisalvo]

Hi C Moore.

The Dropdown is not working. In the dropdown, I am using validation INDIRECT(COLUMN NEXT to DROP DOWN) The Column next to the drop down is all A Values to trigger the DropDown

 

[C Moore]

Not sure what you're asking/saying. Indirect converts text to a reference, so not sure of your use here.