Defined list of values

[t16turbo]

I have defined the results of my query as a Name, "Names"

the list is in Column A, and is refreshed from a query.
In the Refers to box, I have written:

=OFFSET(INDIRECT("Managers!$A$2"),  0,  0, COUNTA(Managers!$A:$A)-1,   1 )

In a combo box on another worksheet I have typed 'Names' into the 'ListFillRange' property.

The list is picking up the entire Column A, rather than just the data in the list..
can anyone tell me what I am doing wrong?

 

[xlbo]

do you have other data in that column then ??

If so, that is why - the COUNTA counts ALL entries in the column and then extends down that number of rows from the original position

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[t16turbo]

the list is just a query that refreshs, so there shouldn't be any other data in there.
Is there a way that I can make the Name definitaion only look at cells with a length longer than 1 character?

 

[xlbo]

use SUMPRODUCT instead of COUNTA then

=OFFSET(INDIRECT("Managers!$A$2"), 0, 0, SUMPRODUCT((len(Managers!$A$1:$A$65536)>0*(1)))-1, 1 )

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[t16turbo]

Thanks Geoff!

do you know if the named range will update as the list of values changes?

 

[xlbo]

yes - the whole point is that it is dynamic

Look at the OFFSET function in help - it should clarify things

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question