Re: [libreoffice-users] Re: Calc stops refreshing formula halfway down the spreadsheet

At 07:32 12/09/2011 -0700, Ant Christy wrote:
> I haven't found a good answer yet, but I have figured out a few more 
> symptoms. The reason the formula stops refreshing is because the 
> formatting of the cells is changed halfway through the spreadsheet.

I didn't address this question earlier as I had no idea what you 
meant by "refreshing".

> Basically the formula set up is matching numbers in column D to 
> numbers in column A and using COUNTIF to total them. The numbers 
> that we're working with are zip codes, so some start with zero.

Aha!  Now things become clear.  As has already been suggested, it is 
wrong to think of zip codes as numbers, as you will never want to do 
arithmetic with them.  (If you doubt that, ask yourself what it means 
if your friend's zip code is exactly twice yours.  The fact that 
leading zeroes are significant is another clue.)  No, zip codes are 
text items that just happen to be made up only of digits.

> In order to hold that place all cells start with an apostrophe.

That is the way that you can force what you type to be interpreted as 
text even when the cell itself is not so formatted.

> The formatting change that's occurring is after 175 lines Libre 
> removes the apostrophe.

Er, is it really Calc that is doing that?  Or were the values 
originally entered differently?  If later codes do not start with 
zero, perhaps no care was taken to force the values to be text: are 
these values actually stored as numbers?

> The original Excel document has the cells formatted as "number 
> stored as text". Is there a way to do this in Libre?

Yes.  Here are two ways:
o  Format your cells as Text before you type entries.
o  Type an apostrophe (it must be a plain one, not a "smart" one) 
before your numeric values.
These techniques are no help, though, if you are pasting material 
into cells or if you inherit the data.

If you have existing numbers that you need to be text, you will want 
a simple way of converting them.  Are these five-digit zip codes?  Put
=TEXT(A1;"00000")
into B1 and fill it down the column.  This will convert your numbers 
to five-digit text strings - but will mess up any text values it 
finds.  In order to handle both types of data, you could use
=IF(ISTEXT(A1);A1;TEXT(A1;"00000"))
instead.  You can paste the results of these formulae back over your 
original data using Paste Special... and selecting Numbers but not Formulae.

There are two useful tricks that you can use which would have drawn 
your attention to the nature of your problem sooner:
o  Remove any horizontal alignment applied to the cells: click the 
highlighted button in the Formatting toolbar to achieve this.  Text 
values will then be left-aligned and numeric values right-aligned.
o  Go to View | Value Highlighting (or Ctrl+F8).  Text values now 
appear in black and numeric values in blue (and formulae in green).

I trust this helps.

Brian Barker


--
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted