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)

Shouldn't it be COUNTIF not COUNTA?

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=535194

Hi John,

What's in cell F7? If it's empty, you probably won't get anything, since the
formula will be saying to look up 0 rows of employees.

I have no idea what the "<" is for - it seems to result in the COUNTA
formula returning at least 1, but then 1 gets deducted from the COUNTA
result. I think you'd get the same result by replacing
'(COUNTA(Employees,"<")-1)' with 'COUNTA(Employees)'. If the intention was
to include all employee rows, plus an empty row, you'd use either
'COUNTA(Employees,1)' or '(COUNTA(Employees)+1)'

Cheers


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

Thanks Guys

There is an "empty" row in my Named Range, thus I'm assuming that the -1
eliminates this empty row when I go into the drop down.

Cell F7 contains a date, in the format dd/mm/yy

I've now just shut down Excel restarted and opened up the file, and the Drop
down list now gives me the employees that I expect and I changed nothing -
strange


"macropod" wrote in message
...
Hi John,

What's in cell F7? If it's empty, you probably won't get anything, since
the
formula will be saying to look up 0 rows of employees.

I have no idea what the "<" is for - it seems to result in the COUNTA
formula returning at least 1, but then 1 gets deducted from the COUNTA
result. I think you'd get the same result by replacing
'(COUNTA(Employees,"<")-1)' with 'COUNTA(Employees)'. If the intention
was
to include all employee rows, plus an empty row, you'd use either
'COUNTA(Employees,1)' or '(COUNTA(Employees)+1)'

Cheers


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

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)

Arvi

Thanks for your detailed and informative reply


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

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)

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)

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)