Hi

Yes, you can. But you have to define the range you are referring to as a
named range, and use it. P.e. you define a named range MyRange as
=OFFSET(Sheetname!$A$1,1,,COUNTA(Sheetname!$A:$A)-1,1)
now you can anywhere in your workbook create data validation list(s) with
source
=MyRange


Arvi Laanemets


"John" wrote in message
...
Arvi

One query is it correct that you can't reference another sheet within Data
Validation criteria (other than the one I am on)?


"Arvi Laanemets" wrote in message
...
Hi

This is a formula to determine a dynamic range. But somehow twisted one.

At start, the 1st parameter for OFFSET function must be a cell
referense,
as
it determines the starting cell on sheet, from where the returned range
is
calculated. As you use the same range Employees in COUNTA too, then
obviously Employees is a range containing several cells

So the formula mus start like this
=OFFSET(Sheetname!$A$2,....

Next 2 parameters are OK, they say that the start cell for result range
is
not dislocated, i.e. it remains same as determined by 1st parameter.

The 3rd parameter {(COUNTA(Employees,"<")-1)*($F$7<"") in your
example}determines the number of rows in result range, and the 4rth one
the
number of returned columns. From your expression follows, that when $F$7
is
empty, an empty range (0 rows) is returned. But the rest of expression
here
is problematic again. COUNTA function can't have 2 parameters at all.
You
must have there either
(COUNTA(Employees)-1)
or
(COUNTIF(Employees,"<")-1)

Both expressions count all non-empty cells in range Employees, and
return
a
value less of it by 1. Usually -1 in formulas like this is used to take
column header out of account. P.e. when range Employees was defined as
$A$1:$A$100, and in a1 was text for column header, and there were
entries
until cell A20, then the formula
=OFFSET(Sheetname!$A$2,,,COUNTA(Employees)-1,1)
returns the range A2:A20. I myself prefer to use slightly different
formyula - so the range will be not corrupted when you need to delete
the
row 2.
=OFFSET(Sheetname!$A$1,1,,COUNTA(Employees)-1,1)


NB! There may be empty rows at bottom of range Employees, but the filled
range MUST be continuous - otherwise last entries are dropped from
returned
range.


Arvi Laanemets


"John" wrote in message
...
I received this formula via the boards awhile ago, it worked, but now
it
doesn't so I'm not sure why. It simple selects via Data Validation -
Drop
Down a list of employees from a Named Range "Empoyees"

My problem is that it doesn't allow me to select anyone in the Drop
down,

=OFFSET(Employees,0,0,(COUNTA(Employees,"<")-1)*($F$7<""),1)