Dynamic Name Ranges in data validation

  • Thread starter Thread starter Hans
  • Start date Start date
H

Hans

When I make a dynamic name range (with OFFSET) and use
this in a data validation list, no error message appears
when I manually type in data in the data validation cell.
I don't have this problem with normal name ranges.

Is there a way to solve this?

Hans
 
Hi Frank

This is the name formula:

=OFFSET(AccountNumbers!$A$5,0,0,COUNTA(AccountNumbers!
$A:$A),1)

Regards,
Hans
 
Hi Hans
tried your name definition and it works for me. If you have defined the
name: Test_Val with
=OFFSET(AccountNumbers!$A$5,0,0,COUNTA(AccountNumbers!$A:$A),1)

and enter the following formula in the data validation dialog:
=Test_Val

everything works for me. If you still have problems you may mail me
your spreadsheet and I'll set it up for you
(frank[dot]kabel[at]freenet[dot]de)
 
You're starting at A5, but counting from A1. If A1:A4 have anything in them,
then your range includes some blank cells.

Hit F5 (edit|Goto) and type in the name that you're using.

It should select that range. Did you get what you expected?

If no, you could modify your dynamic range (if A1:A4 are filled in):
=OFFSET(AccountNumbers!$A$5,0,0,COUNTA(AccountNumbers!$A:$A)-4,1)

(note the extra -4 in the formula).

If you can't do that, in the data|validation dialog, you can uncheck the:
Ignore Blank (on the Settings tab).
 
Hi
Dave
sorry should have posted this also to the NG: The OP sent me his file
and you're right on the spot: It was the name definition. Changing this
to your suggestion or to
=OFFSET(AccountNumbers!$A$5,0,0,COUNTA(AccountNumbers!$A5:$A10000),1)
solved the problem
 
Back
Top