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 On 12/04/11 07:18, Chris Gummo wrote: > 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 > <[email protected]>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 <[email protected] >>> 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 >>>> <[email protected]> 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 [email protected] >>>>> 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 [email protected] >>>> 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 [email protected] >> 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 [email protected] 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
