Data Validation issue with dynamic named range

Jek61

New Member
Joined
Jul 30, 2020
Messages
12
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi

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

Jek61

New Member
Joined
Jul 30, 2020
Messages
12
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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

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

Jek61

New Member
Joined
Jul 30, 2020
Messages
12
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

1602784458937.png
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
What is the result of the formula



Excel Formula:

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

Jek61

New Member
Joined
Jul 30, 2020
Messages
12
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,089
Messages
5,622,641
Members
415,916
Latest member
eugenia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top