INDIRECT and CONCATENATE in Defined Name
 

I'm trying to set up a defined name that will be a list whose number of items
will change over time. The list range is A1:A200, but only has items in
A1-A100 now - more will be added as the need arises.

I've tried defining the name where "Refers to" is
=INDIRECT(CONCATENATE("A1:A",COUNTA(A1:A200)),
or variations thereof, but all I get is an empty list.

I'm using this defined name for data validation, and when I select the
dropdown for the cell, since the cell is blank, the dropdown positions itself
at the first blank entry in the list, or A101, whereas I want it to position
itself at the first entry in the list. Perhaps there is a different way of
going about this.

--
Bill @ UAMS

INDIRECT and CONCATENATE in Defined Name
 

My mistake - it does work.

--
Bill @ UAMS


"BillCPA" wrote:

I'm trying to set up a defined name that will be a list whose number of items
will change over time. The list range is A1:A200, but only has items in
A1-A100 now - more will be added as the need arises.

I've tried defining the name where "Refers to" is
=INDIRECT(CONCATENATE("A1:A",COUNTA(A1:A200)),
or variations thereof, but all I get is an empty list.

I'm using this defined name for data validation, and when I select the
dropdown for the cell, since the cell is blank, the dropdown positions itself
at the first blank entry in the list, or A101, whereas I want it to position
itself at the first entry in the list. Perhaps there is a different way of
going about this.

--
Bill @ UAMS

INDIRECT and CONCATENATE in Defined Name
 

Try
=INDIRECT(CONCATENATE("A1:A",COUNTA(A1:A200)))

Your formula is missing the last )

See Debra's article at http://www.contextures.com/xlNames03.html for ideas...

=INDIRECT(CONCATENATE("A1:A",COUNTA(A1:A200))

"BillCPA" wrote:

I'm trying to set up a defined name that will be a list whose number of items
will change over time. The list range is A1:A200, but only has items in
A1-A100 now - more will be added as the need arises.

I've tried defining the name where "Refers to" is
=INDIRECT(CONCATENATE("A1:A",COUNTA(A1:A200)),
or variations thereof, but all I get is an empty list.

I'm using this defined name for data validation, and when I select the
dropdown for the cell, since the cell is blank, the dropdown positions itself
at the first blank entry in the list, or A101, whereas I want it to position
itself at the first entry in the list. Perhaps there is a different way of
going about this.

--
Bill @ UAMS