Dropdown

[shyamvinjamuri]

Hi

I have a dropdown in Sheet1 column A which has names which increase or decrease.
Is there a way to show the names based on the last filled row in Column A?

Thanks

 

[T. Valko]

Hi

I have a dropdown in Sheet1 column A which has names which increase or decrease.
Is there a way to show the names based on the last filled row in Column A?

Thanks

Do you mean that you want a dynamic range?

Maybe something like this...

Names in the range A2:An

=A2:INDEX(A2:A1000,MATCH("zzzzz",A2:A1000))

Adjust for a reasonable end of range.

 

[shyamvinjamuri]

Thanks.

What I am looking for is this.
Instead of A2:A1000, A2:A(last filled row in column A)
Last filled row is dynamic. it could be any number.

Thanks

 

[Jeffrey Smith]

You could also use some Code in the sheet to change the Named Range each time the column of names change. Name the Range you have your column of names and set the listfillrange in the properties of the pulldown box to be that named range. Add the code below to the sheet. Of course you can change the location where the names are stored.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Range, R As Range
  Set i = Intersect(Target, Range("B1:B1000"))
  
  If Not i Is Nothing Then
    Application.EnableEvents = False
    Set R = Sheets("Sheet1").Range(Range("B1"), Range("B65000").End(xlUp))
    ActiveWorkbook.Names("Names").RefersToR1C1 = R
    Application.EnableEvents = True
  End If
  
End Sub

 

[T. Valko]

Thanks.

What I am looking for is this.
Instead of A2:A1000, A2:A(last filled row in column A)
Last filled row is dynamic. it could be any number.

Thanks

Ok, that's exactly what my suggestion will do.

It's creating a smaller dynamic range from a larger range. The A1000 refers to the end of the larger range. Just adjust that so the formula isn't wasting "its time" looking in a bunch of empty cells for something that doesn't exist.

For example, if you know for certain that the number of names in the range will never be more than 100, then use A100 as the end of the larger range:

=A2:INDEX(A2:A100,MATCH("zzzzz",A2:A100))

MATCH("zzzzz",A2:A100) will find the last TEXT entry in the range A2:A100. Let's assume that last TEXT entry is in cell A59. Then the formula returns the dynamic range A2:A59.

 

[shyamvinjamuri]

Thank you both for your help.

Thanks again

 

[T. Valko]

Thank you both for your help.

Thanks again

You're welcome!