Dynamic Named Range, problem with data validation

[Jek61no]

Hi Folks,

I am a newbee so please excuse any slips in rules.

I have used the offset function to name a range which works fine. I have tried to use this "valid" name in a data validation selection. After entering the named source box excel reports that "The source evaluates to an error .." although this is not the case as it evaluates as expected.

=offset(D2,0,0,COUNTA(D:D)-COUNTBLANK(D1:INDIRECT("D"&COUNTA(D:D)))1)

This a saved as the name listlabour , in the validation option I have used list and the source as =listlabour.

Does anyone have any ideas ?

 

[Jek61no]

Hi Markmzz,

thanks for the code I will give it a try. My main issue is why excel will accept a valid name which appears in name manager and when I click on source will highlight the appropriate range., but we I use this name reference in the data validation section t thrown up an error claiming that the original name reference contains errors.

Regards

Jek.

 

[Aladin Akyurek]

Hi Aladin,

I have column A say

JAD
XAD
JAD
KAD
LAD
XAD
VAD

Column D delivers

JAD
KAD
LAD
VAD
XAD

Giving a unique alphabetical list from column A, this then provides the source for the validation drop down.

Jek.

Define listlabour using Formulas | Name Manager as referring to:

=Sheet1!$D$2:INDEX(Sheet1!$D:$D,MATCH("*",Sheet1!$D:$D,-1))

Adjust the sheet name to suit. This is fast and effective.

 

[markmzz]

Hi Markmzz,

thanks for the code I will give it a try. My main issue is why excel will accept a valid name which appears in name manager and when I click on source will highlight the appropriate range., but we I use this name reference in the data validation section t thrown up an error claiming that the original name reference contains errors.

Regards

Jek.

Hi Jek,

Try this:

=OFFSET($D$2,0,0,COUNTA($D:$D)-COUNTBLANK($D$1:INDIRECT("D"&COUNTA($D:$D)))-1)

The problem is the relative name (with relative references).

More information about relative and absolute references:

About cell and range references - Excel
Switch between relative, absolute, and mixed references - Excel

Markmzz

 

[Jek61no]

Many Thanks Markmzz,

I will give this a try, I have found a work around which I can post.

Many thanks foe all your assistance.

Jek.

 

[Aladin Akyurek]

A workaround for what? I must be missing something...

 

[Jek61no]

Hi Aladin,

my original question was why a valid name range gave an error message when used in the data validation option. The many suggestions of alternative methods to achieve the same result assisted in achieving the result, thus "worked around" the issue, although my initial question remains unanswered.

I am grateful to all contributors and we have collectively achieved he result I needed.

I hope this clears things up.

Jk.

 

[markmzz]

I have used the offset function to name a range which works fine. I have tried to use this "valid" name in a data validation selection. After entering the named source box excel reports that "The source evaluates to an error .." although this is not the case as it evaluates as expected.

=offset(D2,0,0,COUNTA(D:D)-COUNTBLANK(D1:INDIRECT("D"&COUNTA(D:D)))1)

This a saved as the name listlabour , in the validation option I have used list and the source as =listlabour.

Does anyone have any ideas ?

[COLOR="#0000FF"]=OFFSET($D$2,0,0,COUNTA($D:$D)-COUNTBLANK($D$1:INDIRECT("D"&COUNTA($D:$D)))-1)
[/COLOR]

The problem is the relative name (with relative references).

More information about relative and absolute references:

About cell and range references - Excel
Switch between relative, absolute, and mixed references - Excel

Hi Jek,

I think that you are missing some thing.

Look at my previous post above and compare my formula (in blue) with your formula of your first post (in red).

My formula give a absolute name and your formula give a relative name.

Markmzz

 

[Aladin Akyurek]

Hi Aladin,

my original question was why a valid name range gave an error message when used in the data validation option. The many suggestions of alternative methods to achieve the same result assisted in achieving the result, thus "worked around" the issue, although my initial question remains unanswered.

I am grateful to all contributors and we have collectively achieved he result I needed.

I hope this clears things up.

Jk.

=OFFSET(D2,0,0,COUNTA(D:D)-COUNTBLANK(D1:INDIRECT("D"&COUNTA(D:D))),1)

corrected for the missing comma works for me, also when used in data validation as source. However, it's is quite volatile, therefore inefficient. Similar formulas, insistently put up, have the same fate. The one I forwarded is definitely less so.

 

[Jek61no]

Markzz,

your suggestion of using absolute name still evaluates to an error when used in the data validation option.

Jek,

 

[markmzz]

Markzz,

your suggestion of using absolute name still evaluates to an error when used in the data validation option.

Jek,

Here all is ok.

Verify if your name is like this:

listlabour

Refers To: [COLOR=#0000ff]=OFFSET('Sheet 1'!$D$2,0,0,COUNTA('Sheet 1'!$D:$D)-COUNTBLANK('Sheet 1'!$D$1:INDIRECT("D"&COUNTA('Sheet 1'!$D:$D)))-1)
[/COLOR]

Note: use your sheet name instead of Sheet 1.

Markmzz