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 ?

 

[pgc01]

Hi
Welcome to the board

4 questions:

1 - you are missing a comma before the last parameter of the offset. Was it a typo when you posted?

2 - it's tricky to work with named ranges based on relative addresses. Are you sure it's what you want?
The usual for the named range would be to have absolute addresses.

3 - you are basing the offset on the cell D2, but you are counting the cells from D1. Is this really what you want?

4 - can you explain which is the range you want to define with the named range?

 

[Jek61no]

Hi PGC,

sorry this was a typo and the 1 at the end should so also be -1.

I need the address to be relative as I need an expandable name range.

D1 has a title which I need to exclude from the named range.

I have created a unique, sorted list from a large list of duplicate entries, this list provides the basis for the validation drop down list. I have used a formula to extract the unique list, this creates "blanks" which are present I the validation drop down, thus I have tried to exclude these "blanks" by using the offset formula.

 

[pgc01]

Hi

Try for the definition of the named range:

=OFFSET(Sheet1!$D$2,0,0,ROWS(Sheet1!$D$2:$D$1000)-COUNTBLANK(Sheet1!$D$2:$D$1000),1)

1 - amend the sheet name if necessary

2 - it's bad practice to use whole columns in formulas, it's inefficient and can make the workbook sluggish. I used rows 2:1000 in the formula, if you think it's not enough use a bigger upper bound.

 

[Jek61no]

Cheers Many thanks,

I will give it try

Jek

 

[Aladin Akyurek]

Since you seem to define istlabour as a source list in data validation, I'd expect no blanks in between the items of interest in column D and there are no entries in this column other than those of interest. Is this the right assumption?

 

[Jek61no]

Hi Aladin,

this is assumption is not correct I have extracted a unique list into column D using an extract formula, the formula I column D must extend beyond the duplicates list (in column A) to ensure that any new entries are added to the unique list in column D. The presence of the formula means he cells are "not blank", thus I needed to find another route to exclude these cells from my drop down box, otherwise I get blanks in the validation box

Hope this make sense.

Jek

 

[Aladin Akyurek]

Hi Aladin,

this is assumption is not correct I have extracted a unique list into column D using an extract formula, the formula I column D must extend beyond the duplicates list (in column A) to ensure that any new entries are added to the unique list in column D. The presence of the formula means he cells are "not blank", thus I needed to find another route to exclude these cells from my drop down box, otherwise I get blanks in the validation box

Hope this make sense.

Jek

Are you saying that you have something like:

JAD
NAD
VAD

KAD
LAD
XAD

and not:

JAD
NAD
VAD
KAD
LAD
XAD

 

[markmzz]

Try this too:

=Sheet1!$D$2:INDEX(Sheet1!$D:$D,MATCH(2,INDEX(1/(Sheet1!$D$1:$D$1000<>""),)))

Or

=Sheet1!$D$2:INDEX(Sheet1!$D:$D,LOOKUP(2,1/(Sheet1!$D$1:$D$1000<>""),ROW(Sheet1!$D$1:$D$1000)))

Markmzz

 

[Jek61no]

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.