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,

I did adjusted the formula for my sheet ("lab").

To be clear both of our formulae achieve the first part of my goal in that they highlight a range of cells "dynamically". When testing your formula within my sheet I have given your formula the name markmzz in the "name manager". Therefore the name is markmzz and in the "Refers to box" I have copied your formula which woks perfectly (so far so good).

Then I go to the Data validation option on the Data Tab I select the Allow as list and the source as =markmzz (as defined in the name manager), this then throws up the error message "The Source currently evaluates to an error. Do you want to continue ?"

So return to the position from which I started.

AS I have noted in a previous post I have a solution which achieves a similar end result but not as "tidy" as I would have liked.

Thanks for all the dialogue, but I sure there are bigger and better problems out "There"

Speak to you again.

Jek.

 

[markmzz]

Hi Markmzz,

I did adjusted the formula for my sheet ("lab").

To be clear both of our formulae achieve the first part of my goal in that they highlight a range of cells "dynamically". When testing your formula within my sheet I have given your formula the name markmzz in the "name manager". Therefore the name is markmzz and in the "Refers to box" I have copied your formula which woks perfectly (so far so good).

Then I go to the Data validation option on the Data Tab I select the Allow as list and the source as =markmzz (as defined in the name manager), this then throws up the error message "The Source currently evaluates to an error. Do you want to continue ?"

So return to the position from which I started.

AS I have noted in a previous post I have a solution which achieves a similar end result but not as "tidy" as I would have liked.

Thanks for all the dialogue, but I sure there are bigger and better problems out "There"

Speak to you again.

Jek.

Hi Jek,

Very strange what's happening. I tested the formula with multiple versions of Excel (2003+) and it worked perfectly with the data validation feature.

Name

markmzz - Refers To: =OFFSET(lab!$D$2,0,0,COUNTA(lab!$D:$D)-COUNTBLANK(lab!$D$1:INDIRECT("D"&COUNTA(lab!$D:$D)))-1)

If you want, give to me (for PM) your email and I'll send to you my example workbook.

Markmzz

 

[markmzz]

Hi Jek,

I found the problem.

You have to tell Excel the column D sheet (in red).

Here is the final formula:

=OFFSET(lab!$D$2,0,0,COUNTA(lab!$D:$D)-COUNTBLANK(lab!$D$1:INDIRECT("[COLOR="#FF0000"][B]lab![/B][/COLOR]D"&COUNTA(lab!$D:$D)))-1)

Markmzz

 

[sWi7zLe]

Just out of interest... does this work?

I have a similar problem but this does not work for me.

Should this work in 2010?

Cheers

 

[Jek61no]

Hi sWi7zLe,

this solution works well I have now used it many times in 2010.

REgards

Jek61no

Just out of interest... does this work?

I have a similar problem but this does not work for me.

Should this work in 2010?

Cheers

 

[sWi7zLe]

Hmmm, beginning to wonder if my problem is the same now.

Trying to post a thread of my own, but every time I preview it, it keeps deleting code... so very helpful

 

[sWi7zLe]

Woooohooooo! Figured out what I needed...

=OFFSET(Lists!$CB$3,0,0,COUNTA(Lists!$CB$3:$CB$138)-COUNTIF(Lists!$CB$3:$CB$138," "))

Phew, I was going quite mad :D