Question about validation lists
 

What causes the validation list to change the range to add one or more empty
cells to the end of the list. When playing round with the defined Name
formula I notice it can change the range to include empty cells. Anyone got
any thought on this?

=OFFSET(Sheet3!$F$1,0,0,COUNTIF(Sheet3!$F:$F,"?*") )

Question about validation lists
 

If there are blanks in column F followed by data that could cause it.


---
HTH

Bob Phillips

"Gotroots" wrote in message
...
What causes the validation list to change the range to add one or more
empty
cells to the end of the list. When playing round with the defined Name
formula I notice it can change the range to include empty cells. Anyone
got
any thought on this?

=OFFSET(Sheet3!$F$1,0,0,COUNTIF(Sheet3!$F:$F,"?*") )

Question about validation lists
 

What is your reason for using
COUNTIF(Sheet3!$F:$F,"?*")
instead of
COUNTA(Sheet3!$F:$F)

In column F do you have any cell that just conatin blank characters / empty
spaces...
--
HTH...

Jim Thomlinson


"Gotroots" wrote:

What causes the validation list to change the range to add one or more empty
cells to the end of the list. When playing round with the defined Name
formula I notice it can change the range to include empty cells. Anyone got
any thought on this?

=OFFSET(Sheet3!$F$1,0,0,COUNTIF(Sheet3!$F:$F,"?*") )

Question about validation lists
 

The reason for using such a formula is that the range contains values that
are the result of a formula. No blanks occur until the end of the list,
however any empty cells thereafter will contain a formula.

"Jim Thomlinson" wrote:

What is your reason for using
COUNTIF(Sheet3!$F:$F,"?*")
instead of
COUNTA(Sheet3!$F:$F)

In column F do you have any cell that just conatin blank characters / empty
spaces...
--
HTH...

Jim Thomlinson


"Gotroots" wrote:

What causes the validation list to change the range to add one or more empty
cells to the end of the list. When playing round with the defined Name
formula I notice it can change the range to include empty cells. Anyone got
any thought on this?

=OFFSET(Sheet3!$F$1,0,0,COUNTIF(Sheet3!$F:$F,"?*") )