2011 Archives by date, by thread · List index

# Re: [libreoffice-users] Calc Question

```This formula "=COUNTIF(\$F\$4:\$K\$380;F4)=1" is actually used in the Data
Validation section in Excel and looks through cells F4 through K380 and does
not allow us to input multiple serial numbers for equipment. For instance,
we scan a serial number in cell F4 and try to scan the same number in cell
F8 then we will get an error message saying that we already scanned that
serial number in. I tried to do the same thing in Calc but it does not allow
me to make a custom criteria under DATA>VALIDITY. As for the "=1" at the end
I am not sure why it is there. I have been going with the status quo under i
was task to find an alternative to MS Office. Lucky me I guess.

Chris

On Mon, Apr 11, 2011 at 2:49 PM, Steve Edmonds
<steve.edmonds@ptglobal.com>wrote:

```
```It seems like a few others here I tried your formula.
the =1 at the end of  =COUNTIF(F4;K380,F4)=1 makes it a TRUE/FALSE
evaluation.
I tried to use this as validation for input but it didn't seem to work
without considering a macro.

Could you please explain how this formula is used.
i.e. I assume data is input into cells F4 to K380.  What cell number is
this formula in?
Cheers,steve

On 12/04/11 04:50, Chris Gummo wrote:
```
```I have tried the formula below with the \$ signs and I only get a TRUE or
FALSE answer when I type this into the cell. Maybe I am doing something
wrong which is a very good possibility. We have been using this code
```
```since
```
```before I started working here and I am not too strong with formulas like
this. I can do the easy ones with no problem. Could you let me know how
```
```you
```
```did it since it worked for you?

On Sun, Apr 10, 2011 at 6:11 PM, Roger T. Imai <roger.t.imai@gmail.com
wrote:

```
```Aha! It WAS the separator. Regina's answer to Chris Gummo is helpful
to me also. Thank you.

On Sun, Apr 10, 2011 at 4:40 PM, Regina Henschel
<rb.henschel@t-online.de> wrote:

```
```Hi Chris,

Chris Gummo schrieb:

```
```The formula that we use is below. We use it in the Data Vailidation
section
and not in every cell. I have not been able to get it to work.

=COUNTIF(F4;K380,F4)=1

```
```Please have a look at the separators. I guess it should be
=COUNTIF(F4:K380;F4)=1

If you want to copy&paste the format, then I expect
=COUNTIF(\$F\$4:\$K\$380;F4)=1

Kind regards
Regina

--
Unsubscribe instructions: E-mail to users+help@libreoffice.org
Posting guidelines + more:
```
```http://wiki.documentfoundation.org/Netiquette
```
```List archive: http://listarchives.libreoffice.org/www/users/
All messages sent to this list will be publicly archived and cannot be
deleted

```
```--
Unsubscribe instructions: E-mail to users+help@libreoffice.org
Posting guidelines + more:
```
```http://wiki.documentfoundation.org/Netiquette
```
```List archive: http://listarchives.libreoffice.org/www/users/
All messages sent to this list will be publicly archived and cannot be
deleted

```
```

```
```
--
Unsubscribe instructions: E-mail to users+help@libreoffice.org
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/www/users/
All messages sent to this list will be publicly archived and cannot be
deleted

```
```
--
Unsubscribe instructions: E-mail to users+help@libreoffice.org
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/www/users/
All messages sent to this list will be publicly archived and cannot be deleted
```