Dynamic Named Range, problem with data validation

Jek61no

New Member
Joined
Apr 12, 2014
Messages
16
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 ?
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Try this too:

Code:
=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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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
Back
Top