Calc Question

users
#1

My company is in the process of moving to LibreOffice from MS Office. We do
inventory of equipment from our customers and in Excel we have a formula
that does not allow us to scan in duplicate serial numbers. Does any one
know of a way to do this using Calc in LibreOffice?

Chris

#2

fre 2011-04-08 klockan 15:15 -0400 skrev Chris Gummo:

My company is in the process of moving to LibreOffice from MS Office. We do
inventory of equipment from our customers and in Excel we have a formula
that does not allow us to scan in duplicate serial numbers. Does any one
know of a way to do this using Calc in LibreOffice?

Chris

I guess that could be done in several ways. What does that formula look
like in Excel?

#3

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

Thanks
Chris

#4

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

#5

Chris,

More information please: in LibreOffice Calc, Tools > Options >
LibreOffice Calc > Formula ...have you changed Formula Syntax from the
default Calc A1 to Excel A1 (or perhaps Excel R1C1 for references to
other worksheets)?

I'm no spreadsheet expert, but I've found that most of my formula
problems using XLS files in OpenOffice/LibreOffice have turned out to
be with the separators in the formulas. I had been changing the
separators in my Excel worksheets, and only very recently discovered
that I could actually choose the formula syntax to match Excel's.

Here is Excel information on COUNTIF, btw.
http://office.microsoft.com/en-us/excel-help/countif-HP005209029.aspx

--Roger

#6

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

SHOW FORMULAS ONLY SHOWS FORMULAS IN BOTTOM ROW<<<

I have another problem in LibreOffice Calc on Ubuntu 10.10. One can
display all formulas in a worksheet: Calc > Tools > Options >
LibreOffice Calc > View > Display > (select) Formulas. The quick
keyboard toggle for this feature is Ctrl+~ (Control key + tilde
character on the left of the 1 key.)

However in my worksheets, the show formula option only seems to
display the formulas in the last (bottom) line of the worksheet. I
have not been able to find anywhere that this has been reported. If
this is a bug, I would appreciate it if it would be fixed, as this is
a most useful feature for troubleshooting worksheets.

Note: I am not very familiar with participating in mailing lists, and
not sure of how participants keep track of multiple discussions. If
I'm doing this wrong, please feel free to give me a clue. Well, more
than a clue... Thanks!

Roger

#7

sön 2011-04-10 klockan 16:52 -0400 skrev Chris Gummo:

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

Thanks
Chris

Sorry for being some kind of an idiot, but what does the ”=1” mean at
the end of the formula?

#8

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?

#9

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

#10

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

#11

I suggest going to the help section and entering "data validity" in the
search box. This worked for me in OpenOffice.org.

nvsoar

#12

Hi Chris,

Chris Gummo schrieb:

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.

Is it necessary to prevent entering? If you only want to be informed about double entries, you can use conditional formatting. For example set a red background if "=COUNTIF($F$4:$K$380;F4)>1" is true.

Kind regards
Regina

#13

Hi nvsoar,

nvrk schrieb:

I suggest going to the help section and entering "data validity" in the
search box. This worked for me in OpenOffice.org.

how will you do it? Data validity needs a list of valid numbers, but here all already existing values are invalid and new valid numbers are unknown.

Kind regards
Regina

#14

Hi Chris.
I came up with the solution that Regina proposes. Another solution was
to have the countif statements in other hidden cells and use a macro to
pop a message if you get duplicate data (but conditional background
should be as obvious).
It is possible to use the countif in the data validation in the data
field of the criteria tab but LO precedence may not match Excell
precedence (I don't have excell) in evaluation of the statement.

i.e. if I use COUNTIF(F4;K380,F4) in the data validation it uses the
data in currently (previously entered) in F4 rather than the data I wish
to enter into F4, the data you are trying to enter is not available to
the statements in the data field of the criteria tab. Not sure that
makes much sense but I will try to explain.
If the sequence in LO for data validation was as below I think it would
be easier;
Enter data in cell,
press enter or click another cell,
data is placed in cell,
value (in the criteria tab of validation) is calculated,
if value meets the requirements, allow entry, if value does not meet the
requirements, back out the entry (for stop) or issue warning.

It would also be useful if the Data field in the criteria tab of
validation had TRUE and FALSE as options, but to use these the data that
you are attempting to input must be available to the statements in the
data field.

steve

#15

I believe the expresion is a boolean expresion where you compare if the COUNTIF(F4;K380,F4) is equal to 1.
It means that if the count of cells in the range F4:K380 having the value matching the contents of F4 is 1 (only 1 such a cell) let the result is true.

There is a typo in the syntax; it should be =COUNTIF(F4:K380;F4)=1

Regards,
Jiri

Dne 11.4.2011 18:01, Johnny Rosenberg napsal(a):