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