Data Validation issue with dynamic named range

[Jek61]

HI,

I have an interesting issue with Data Validation used in conjuction with a named range.

I have a Calcs worksheet and an Appoint_list worksheet within the same workbook. The Calcs worksheet conatins a list of Clients and the Appoint_list worksheet has a data validation cell which access the Client list called Appoints.

The named range which I have used is called Appoints and is a dynamic named range with the following fornula =OFFSET(Calcs!$I$2,0,0,COUNTIF(Calcs!I:I,"?*")-1,1), I have used the Countif(Calcs!i:i,"?*") to exclude blank cells (otherwise the cells that look blank but conatin a formula and are included and the validation list contains many blank spaces.). When I use this named range I only see 1 name in the validation drop down list i.e. the first name on the list. If I hard code the range =Calcs!$I$2:$I$15 the list works fine.

I am sure I have used Offset before in these circumstances so I assume the Countif(Calc!i:i,"?*") must be issue.

Can anyone assist ?

Many thanks

Jek61no

 

[pgc01]

Hi

You are testing strings in Calcs!I:I. Maybe the values in that column are not strings?

 

[Jek61]

Hi

You are testing strings in Calcs!I:I. Maybe the values in that column are not strings?

Hi pgc01,

the named range which includes Calc!i:1 works perfectly when used as a standalone and the correct range is selected, it is only when used in conjunction with the Data validation aspect that it fails.

jel61no

 

[pgc01]

You did not say if the values in Calc!I:I are strings, like I posted. If they are number values the COUNTIF() will not work.


What is the result of the formula

=COUNTIF(Calc!I:I,"?*")

 

[Jek61]

Hi,

sorry for not answering your question the range are all strings, I attach a a screen shot of the named range formula and the result when run from the Name Manger dialogue box, as you can see the formula correctly selects the occupied range. This is the Appoints named range. When I incorportate this name as a list and source=Appoints in the validation dialogue box only the first record appears when run.

Thanks

Jek61no

 

[pgc01]

What is the result of the formula



Excel Formula:

=COUNTIF(Calc!I:I,"?*")

 

[Jek61]

Hi pgc01,

thanks for getting back to me.

The result in this case is 14, which is the correct number of entries in the list shown in a previous message. I appreciate you taking the time to review this issue, I have found a work around however which used the same formula but place in a cell rather than in the name range reference, I have then used the cell reference as the dynamic delimiter.

Name manger fornula = =OFFSET(Calcs!$I$2,0,0,Calcs!$K$1,1)
Formula in $K$1 =COUNTIF(I:I,"?*")-1

It seems to work fine and am not sure why there were issue in the original formula it may be something to do with Offset being a "volatile" function. In any case thanks for your assistance .

Jek61no

 

[pgc01]

Hi Jek

I'm sorry I could not be of more help.
I don't understand why the formula does not work for you. I tested it and it worked OK for me.

I'm glad you found a solution that is working OK for you.