Hi!

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

No, you can refer to other sheets but you have to do it by creating a
defined name or use the Indirect function.

Biff

"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)