Could any one Explain this Formula

J

John

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

daddylonglegs

Shouldn't it be COUNTIF not COUNTA?

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

macropod

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
 
J

John

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
 
A

Arvi Laanemets

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
 
J

John

Arvi

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

Arvi Laanemets

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
 
B

Biff

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

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

Biff
 
Top