[libreoffice-users] Calc — drawing objects anchor default

[T. R. Valentine]

Although **images** have a default anchor of 'To Cell' it seems
**drawing objects** have a default anchor of 'To Page'.

Is there a way to make the default anchor for drawing objects one of 'To Cell'??


-- 
T. R. Valentine
A rich heart may be under a poor coat.

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc auto complete from a list of cells

[John Kaufmann]

Dave,

On 2024-05-29 16:38, dboland9 wrote:

I was asked how to make an auto complete from a list of cells in another sheet 
in the same file. I thought the validation would work, but it only supports a 
scroll bar. That is fine for short lists, but not for hundreds of lines. Any 
suggestions on how to do this? I have seen it in some apps. ...


Trying to understand what is needed, I would ask if you can give an example of 
what you have seen in some apps.

Thanks,
John

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] calc

[Vince@Verizon]

Be sure to include the *$* symbol in the Array.

Remember: the Index, *2*, represents the (second) column, which is 
referenced to the leftmost column within the (locked) Array, *$A$2:$B$4*.


NOTE: The data in column *A *need not be only a numeric value; the data 
may be text also.




Op 7 jun. 2024, om 19:11 heeft Ekerette Ekpo  het volgende 
geschreven:

How did you do it with vlookup? I tried it out and ended up with a #N/A in
cell B4.



--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] calc

[Rob Jasper]

=VLOOKUP(A4;$A$1:$B$3;2;0)

The last argument (0) makes VLOOKUP find an exact match to whatever is in A4.
This means that if A4 is empty, or contains a value that does not exist in the 
range it will respond with N/A



> Op 7 jun. 2024, om 19:11 heeft Ekerette Ekpo  het 
> volgende geschreven:
> 
> How did you do it with vlookup? I tried it out and ended up with a #N/A in
> cell B4.
> 
> On Thu, 6 Jun 2024, 22:33 James,  wrote:
> 
>> Thanks, got vlookup to do what I want. :-)
>> 
>> --
>> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
>> Problems?
>> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
>> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
>> List archive: https://listarchives.libreoffice.org/global/users/
>> Privacy Policy: https://www.documentfoundation.org/privacy
>> 
> 
> -- 
> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> Problems? 
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy


-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] calc

[Ekerette Ekpo]

How did you do it with vlookup? I tried it out and ended up with a #N/A in
cell B4.

On Thu, 6 Jun 2024, 22:33 James,  wrote:

> Thanks, got vlookup to do what I want. :-)
>
> --
> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> Problems?
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy
>

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] calc

[James]

Thanks, got vlookup to do what I want. :-)

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] calc

[hymie!]

I believe what OP wants is  "Give me the contents of the cell at Column B,
Row the-number-that-appears-in-cell-A4"

I think it can be done with CONCAT but I wouldn't bet money on it.

--hymie!

On Thu, Jun 06, 2024 at 12:43:17PM +0200, Rob Jasper wrote:
> If I understand you correctly =VLOOKUP(A4;$A$1:$B$3;2;0) in B4 will do what 
> you want
> 
> 
> > Op 6 jun. 2024, om 05:33 heeft James  het volgende 
> > geschreven:
> > 
> > Say I have column A with the numbers 1, 2, 3:
> > A1 (1)
> > A2 (2)
> > A3 (3)
> > and column B with the numbers 11, 22, 33:
> > B1 (11)
> > B2 (22)
> > B3 (33)
> > 
> > Column A Row 4 contains a reference to one of row 1, 2, or 3 (I switch 
> > which row is referenced so I can see the effects of different choices).
> > 
> > Is there a calc function which can find which row I chose so I 
> > automatically populate row 4, column B?
> > Row 4, column A matches one of the rows 1-3...
> > 
> > -- 
> > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> > Problems? 
> > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> > Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> > List archive: https://listarchives.libreoffice.org/global/users/
> > Privacy Policy: https://www.documentfoundation.org/privacy
> 
> 
> -- 
> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> Problems? 
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy
> 

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] calc

[hymie!]

Ignore me, I think I misunderstodd your "vlookup" command which now
looks right to me.  Sorry.

--hymie!

On Thu, Jun 06, 2024 at 08:10:42AM -0400, hymie! wrote:
> I believe what OP wants is  "Give me the contents of the cell at Column B,
> Row the-number-that-appears-in-cell-A4"
> 
> I think it can be done with CONCAT but I wouldn't bet money on it.
> 
> --hymie!
> 
> On Thu, Jun 06, 2024 at 12:43:17PM +0200, Rob Jasper wrote:
> > If I understand you correctly =VLOOKUP(A4;$A$1:$B$3;2;0) in B4 will do what 
> > you want
> > 
> > 
> > > Op 6 jun. 2024, om 05:33 heeft James  het volgende 
> > > geschreven:
> > > 
> > > Say I have column A with the numbers 1, 2, 3:
> > > A1 (1)
> > > A2 (2)
> > > A3 (3)
> > > and column B with the numbers 11, 22, 33:
> > > B1 (11)
> > > B2 (22)
> > > B3 (33)
> > > 
> > > Column A Row 4 contains a reference to one of row 1, 2, or 3 (I switch 
> > > which row is referenced so I can see the effects of different choices).
> > > 
> > > Is there a calc function which can find which row I chose so I 
> > > automatically populate row 4, column B?
> > > Row 4, column A matches one of the rows 1-3...
> > > 
> > > -- 
> > > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> > > Problems? 
> > > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> > > Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> > > List archive: https://listarchives.libreoffice.org/global/users/
> > > Privacy Policy: https://www.documentfoundation.org/privacy
> > 
> > 
> > -- 
> > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> > Problems? 
> > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> > Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> > List archive: https://listarchives.libreoffice.org/global/users/
> > Privacy Policy: https://www.documentfoundation.org/privacy
> > 

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] calc

[Rob Jasper]

If I understand you correctly =VLOOKUP(A4;$A$1:$B$3;2;0) in B4 will do what you 
want


> Op 6 jun. 2024, om 05:33 heeft James  het volgende 
> geschreven:
> 
> Say I have column A with the numbers 1, 2, 3:
> A1 (1)
> A2 (2)
> A3 (3)
> and column B with the numbers 11, 22, 33:
> B1 (11)
> B2 (22)
> B3 (33)
> 
> Column A Row 4 contains a reference to one of row 1, 2, or 3 (I switch which 
> row is referenced so I can see the effects of different choices).
> 
> Is there a calc function which can find which row I chose so I automatically 
> populate row 4, column B?
> Row 4, column A matches one of the rows 1-3...
> 
> -- 
> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> Problems? 
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy


-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] calc

[Brian Barker]

At 23:33 05/06/2024 -0400, James Lockie wrote:

Say I have column A with the numbers 1, 2, 3:
A1 (1)
A2 (2)
A3 (3)
and column B with the numbers 11, 22, 33:
B1 (11)
B2 (22)
B3 (33)

Column A Row 4 contains a reference to one of row 1, 2, or 3 (I 
switch which row is referenced so I can see the effects of different choices).


It's not clear what you mean by a "reference". I'm guessing you mean 
that A4 contains just one of the numbers (1, 2, or 3) that appear in 
your list, not a true reference, such as "=A1"? Otherwise the problem 
would be trivial.


Is there a calc function which can find which row I chose so I 
automatically populate row 4, column B?


Yup. In B4, enter:
=VLOOKUP(A4;A1:B3;2;FALSE)

VLOOKUP() looks for the value in A4 in the first column of the array 
A1:B3 (i.e. column A) and returns as its result the corresponding 
value in column 2 of the array (i.e. column B). The FALSE parameter 
indicates that the values in column A are not necessarily in 
ascending order. If you know they are in such order, you can use TRUE 
(or omit that parameter); that allows the program to calculate faster 
but also means that a value may be returned even if an exact match 
does not exist - which I'm guessing you wouldn't want.



Row 4, column A matches one of the rows 1-3...


You may want to look into the detail of how to handle cases where the 
value in A4 either does not appear in the list or even appears more than once.


I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] calc: all links broken in existing spreadsheet

[Robert Großkopf]

Hi Tom,


I just upgraded to:

Version: 7.6.7.2 (X86_64) / LibreOffice Community
Build ID: 60(Build:2)
CPU threads: 8; OS: Linux 6.8; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Ubuntu package version: 4:7.6.7-0ubuntu0.23.10.1~bpo22.04.1
Calc: threaded


From which version did you "upgrade".


All links in my major daily planner spreadsheet are now broken. 


Does appear any tooltip if you move the mouse over a link? Something 
like [Ctrl+Click]?


Regards

Robert
--
Homepage: https://www.familiegrosskopf.de/robert


--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] calc

[James]

Say I have column A with the numbers 1, 2, 3:
A1 (1)
A2 (2)
A3 (3)
and column B with the numbers 11, 22, 33:
B1 (11)
B2 (22)
B3 (33)

Column A Row 4 contains a reference to one of row 1, 2, or 3 (I switch 
which row is referenced so I can see the effects of different choices).


Is there a calc function which can find which row I chose so I 
automatically populate row 4, column B?

Row 4, column A matches one of the rows 1-3...

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] calc: all links broken in existing spreadsheet

[Tom Cloyd MS MA]

I just upgraded to:

Version: 7.6.7.2 (X86_64) / LibreOffice Community
Build ID: 60(Build:2)
CPU threads: 8; OS: Linux 6.8; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Ubuntu package version: 4:7.6.7-0ubuntu0.23.10.1~bpo22.04.1
Calc: threaded

All links in my major daily planner spreadsheet are now broken. The same 
is true for all other spreadsheets I've examined.


I have search online help for any information that would resolve the 
problem; none was found.


I have tried redefining a link; it achieves nothing.

This is a major disaster for me.

Any ideas how I can resolve this?

t.

--

~
“Love recognizes no barriers. It jumps hurdles, leaps fences, penetrates walls
to arrive at its destination full of hope.” ~ Maya Angelou
~
Tom Cloyd, MS MA LMHC (WA) | t...@tomcloyd.com
Psychological trauma & dissociative disorders, treatment, research, & advocacy
Spokane, Washington, U.S.A. | (435) 272-3332
https://www.gettraumainfo.com/ (professional)
Facebook: https://www.facebook.com/groups/645665272216298/
www.tomcloyd.com/ (personal)
~
System76 Galago Pro (galp5) laptop
Processors: 8 × 11th Gen Intel® Core™ i7-1165G7 @ 2.80GHz
RAM: 16 GB Dual Channel DDR4 at 3200 MHz
Storage: 500 GIB SSD
Operating System: Pop!_OS 22.04 LTS
Kernel Version: 6.2.6-76060206-generic x86_64
Gnome ver. 42.5
Windowing system: X11
Qt Version: 5.13.3
[updated 2023-05-09:1420]


--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] Calc auto complete from a list of cells

[dboland9]

I was asked how to make an auto complete from a list of cells in another sheet 
in the same file. I thought the validation would work, but it only supports a 
scroll bar. That is fine for short lists, but not for hundreds of lines. Any 
suggestions on how to do this? I have seen it in some apps. I played with 
VLOOKUP, but it doesn't return a list. If there is no good way to do this, can 
it be a feature request?

Thanks,
Dave

Sent with [Proton Mail](https://proton.me/) secure email.
-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] Calc scrolling in slow motion

[Thomas Blasejewicz]

Good afternoon
I do have a problem.
Recently I upgraded to 24.2.3.2, because I wanted to enjoy the 
highlighting of rows and columns in Calc.

Which to me, however, looks horrible and I am not using it now.

Problem:
Since I upgraded, I noticed that when I try scrolling through big Calc 
sheets, but also somewhat bigger Writer files,
that the scrolling becomes sickeningly slow the moment the cursor 
(selected cell) reaches the screen edge.
Meaning, when I scroll down for example, the cursor scrolls/slides down 
the screen in an almost normal fashion, but
when the lower border is reached, it stops for a short rest, moves to 
the next cell - stops for a short rest - moves to the next cell ... forever.

In ALL directions.
Not as marked, but a similar behavior in Writer too.

I tried the aboven mentioned LO version on FOUR machines, three Windows 
10 (basically all the same setting and software installed) and one Linux 
(LMDE6) machines.

Everywhere exactly the same.
Two note books are a little older and one might argue, the hardware is 
not up to the job, but
I am typing this mail using the workstation in my office: 2 XEON 
processors with a total of 16 cores = 32 logical CPUs, 64 GB RAM, 
several TB to spare for storage

and the OS runs from a 500 GB SSD. And I use a brand new monitor.
I refuse to believe, this kind of hardware is "not enough".

So, is the a trick like changing the setting somewhere to make it faster?
Over the past 10-15 years I have been using Libreoffice, I have never 
felt so frustrated with the poor performance.
If there is no trick and the slow motion "the way LO is supposed to work 
now" ... I gladly go back to an earlier version that worked.

This is too much stress.

I DO hope, there is a trick to get a proper behavior.
Thank you
Thomas


--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] Calc Print Preview shows only first page of Print Range

[John Kaufmann]

LO 7.4.2.3 (x64)

Print Range is defined ($A$1:$H$236), with Rows to Repeat ($1:$7), but only the 
first page ($A$1:$H$58) shows in Print Preview. (I don't recall having this 
problem before.) Can someone point me aright?

John

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc basic printing and alignment issues - bug or me?

[LO . Harald . Berger]

Hi Budge,

the print ranges may be set incorrectly.

Page and printer setup in Calc: 
https://wiki.documentfoundation.org/Videos/Page_and_printer_setup_in_Calc


Regards
Harald


Am 10.11.2023 um 10:49 schrieb Budge:
I am working on an openSUSE Leap 15.5 system with LibreOffice Version: 
7.5.4.1 (X86_64) / LibreOffice Community
Build ID: 50(Build:1) (SystemPU threads: 24; OS: Linux 5.14; UI 
render: default; VCL: kf5 (cairo+xcb)

Locale: en-GB (en_GB.UTF-8); UI: en-GB, Calc: threaded.)

I have a very simple spreadsheet with four sheets and data comprising 
mainly text in tables.  In order to maintain the widths of the columns 
I have formatted the cell alignment property with Wrap text 
automatically enabled.
Even so, with one sheet in particular, text is not wrapped and so some 
of the text is hidden unless I increase the width.


In the first column the width is deliberately very wide anyhow so the 
first cell has all the text showing on the one line.
When I print that sheet, the complete long text is not printed, only 
the last few characters of the last word.  The cells below are printed 
correctly.


What have I done wrong and how may I solve these issues please?

Budge.




--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] Calc basic printing and alignment issues - my fault (probably) or bug?

[Budge]

I am working on an openSUSE Leap 15.5 system with LibreOffice Version: 7.5.4.1 
(X86_64) / LibreOffice Community
Build ID: 50(Build:1) (SystemPU threads: 24; OS: Linux 5.14; UI render: 
default; VCL: kf5 (cairo+xcb)
Locale: en-GB (en_GB.UTF-8); UI: en-GB, Calc: threaded.)

I have a very simple spreadsheet with four sheets and data comprising mainly 
text in tables.  In order to maintain the widths of the columns I have 
formatted the cell alignment property with Wrap text automatically enabled.
Even so, with one sheet in particular, text is not wrapped and so some of the 
text is hidden unless I increase the width.

In the first column the width is deliberately very wide anyhow so the first 
cell has all the text showing on the one line.
When I print that sheet, the complete long text is not printed, only the last 
few characters of the last word.  The cells below are printed correctly.

What have I done wrong and how may I solve these issues please?

Budge.

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] Calc basic printing and alignment issues - bug or me?

[Budge]

I am working on an openSUSE Leap 15.5 system with LibreOffice Version: 7.5.4.1 
(X86_64) / LibreOffice Community
Build ID: 50(Build:1) (SystemPU threads: 24; OS: Linux 5.14; UI render: 
default; VCL: kf5 (cairo+xcb)
Locale: en-GB (en_GB.UTF-8); UI: en-GB, Calc: threaded.)

I have a very simple spreadsheet with four sheets and data comprising mainly 
text in tables.  In order to maintain the widths of the columns I have 
formatted the cell alignment property with Wrap text automatically enabled.
Even so, with one sheet in particular, text is not wrapped and so some of the 
text is hidden unless I increase the width.

In the first column the width is deliberately very wide anyhow so the first 
cell has all the text showing on the one line.
When I print that sheet, the complete long text is not printed, only the last 
few characters of the last word.  The cells below are printed correctly.

What have I done wrong and how may I solve these issues please?

Budge.

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc: how to auto-increment sheet names in a cell formula?

[Remy Gauthier]

Hi,
You could name the location of the data on each sheet with a name
created in a way that makes it easy to figure out the name from the row
number. You can then use the INDIRECT() function to retrieve the
information you want.

For example, on the second sheet, the name of the range could be
Source2. You would retrieve the information like this if you have the
formula on row 2:

=INDIRECT(CONCATENATE("Source",ROW())

This will return a reference to the full range, you can then use the
INDEX() function to retrieve the individual values from Ax to Ex.

I hope this helps.
Rémy.

Le mardi 19 septembre 2023 à 23:06 -0700, Tom Cloyd MS MA a écrit :
> I have trying to solve this problem all evening, with no hint of
> success:
> 
> Let's say I have 11 sheets in a Calc spreadsheet. Sheets 2 to 11
> contain 
> data summarized in cells A1:E1 of each sheet.
> 
> I want to capture these summary data in sheet 1, starting with cell 
> A1:E1. But I then want to copy those cells into the next row and have
> the references point to the next SHEET - sheet 3.
> 
> The general problem is that I have a number of sheets from which I
> want 
> to capture such summary data, but without having to manually edit the
> cell formulas each time to get them to point to the next sheet.
> 
> Incidentally, the sheet names are NOT sheet1, sheet2, etc. So moving
> to 
> the next sheet needs to use some general method of incrementing the 
> sheet reference.
> 
> Is there an artful way to do this?
> 
> Thanks for any ideas offered!
> 
> -- 
> 
> ~
> 
> “Love recognizes no barriers. It jumps hurdles, leaps fences,
> penetrates walls
> to arrive at its destination full of hope.” ~ Maya Angelou
> ~
> 
> Tom Cloyd, MS MA LMHC (WA) | t...@tomcloyd.com
> Psychological trauma & dissociative disorders, treatment, research, &
> advocacy
> Spokane, Washington, U.S.A. | (435) 272-3332
> https://www.gettraumainfo.com/ (professional)
> Facebook: https://www.facebook.com/groups/645665272216298/
> www.tomcloyd.com/ (personal)
> ~
> 
> System76 Galago Pro (galp5) laptop
> Processors: 8 × 11th Gen Intel® Core™ i7-1165G7 @ 2.80GHz
> RAM: 16 GB Dual Channel DDR4 at 3200 MHz
> Storage: 500 GIB SSD
> Operating System: Pop!_OS 22.04 LTS
> Kernel Version: 6.2.6-76060206-generic x86_64
> Gnome ver. 42.5
> Windowing system: X11
> Qt Version: 5.13.3
> [updated 2023-05-09:1420]
> 
> 


-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc - how to auto-increment sheet references in a formula?

[Johnny Rosenberg]

Den ons 20 sep. 2023 kl 07:58 skrev Tom Cloyd MS MA :

> I have trying to solve this problem all evening, with no hint of success:
>
> Let's say I have 11 sheets in a Calc spreadsheet. Sheets 2 to 11 contain
> data summarized in cells A1:E1 of each sheet.
>
> I want to capture these summary data in sheet 1, starting with cell
> A1:E1. But I then want to copy those cells into the next row and have
> the references point to the next SHEET - sheet 3.
>
> The general problem is that I have a number of sheets from which I want
> to capture such summary data, but without having to manually edit the
> cell formulas each time to get them to point to the next sheet.
>
> Incidentally, the sheet names are NOT sheet1, sheet2, etc. So moving to
> the next sheet needs to use some general method of incrementing the
> sheet reference.
>
> Is there an artful way to do this?
>
> Thanks for any ideas offered!
>

Maybe there is a way, but I could only do it by defining my own cell
function. It's very simple, here it is:
Function SheetName(i As Integer) As String
SheetName=ThisComponent.getSheets().getByIndex(i).getName()
End Function

One way to use it:
=SHEETNAME(1)
The name of your second sheet will be displayed (0 is the first one).

A way to make it change by filling down:
=SHEETNAME(ROW(A1)-1)

If you want the first sheet to be numbered 1, then just change the function:
Function SheetName(i As Integer) As String
SheetName=ThisComponent.getSheets().getByIndex(i-1).getName()
End Function

Then the last example would look like this:
=SHEETNAME(ROW(A1))

Of course you could make a more advanced function for the whole reference,
that should be doable. Otherwise you can combine this SHEETNAME() function
with functions like ADDRESS() and INDIRECT().
If you input a number higher than the last sheet number you will have an
exception, an error. Of course it's possible to get around that too, for
instance by first counting sheets (I think the total count is already
available, so maybe no counting is needed).


Kind regards

Johnny Rosenberg




>
> --
>
>
> ~
> “Love recognizes no barriers. It jumps hurdles, leaps fences, penetrates
> walls
> to arrive at its destination full of hope.” ~ Maya Angelou
>
> ~
> Tom Cloyd, MS MA LMHC (WA) | t...@tomcloyd.com
> Psychological trauma & dissociative disorders, treatment, research, &
> advocacy
> Spokane, Washington, U.S.A. | (435) 272-3332
> https://www.gettraumainfo.com/ (professional)
> Facebook: https://www.facebook.com/groups/645665272216298/
> www.tomcloyd.com/ (personal)
>
> ~
> System76 Galago Pro (galp5) laptop
> Processors: 8 × 11th Gen Intel® Core™ i7-1165G7 @ 2.80GHz
> RAM: 16 GB Dual Channel DDR4 at 3200 MHz
> Storage: 500 GIB SSD
> Operating System: Pop!_OS 22.04 LTS
> Kernel Version: 6.2.6-76060206-generic x86_64
> Gnome ver. 42.5
> Windowing system: X11
> Qt Version: 5.13.3
> [updated 2023-05-09:1420]
>
>
> --
> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> Problems?
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy
>

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc: how to auto-increment sheet names in a cell formula?

[Michael D. Setzer II]

On 19 Sep 2023 at 23:06, Tom Cloyd MS MA wrote:

Date sent:  Tue, 19 Sep 2023 23:06:41 -0700
To: LibreOffice User's Help Forum 

From:   Tom Cloyd MS MA 

Subject:[libreoffice-users] Calc: how to 
auto-increment sheet names in a cell formula?

> I have trying to solve this problem all evening, with no hint of success:
> 
> Let's say I have 11 sheets in a Calc spreadsheet. Sheets 2 to 11 contain 
> data summarized in cells A1:E1 of each sheet.
> 
> I want to capture these summary data in sheet 1, starting with cell 
> A1:E1. But I then want to copy those cells into the next row and have 
> the references point to the next SHEET - sheet 3.
> 
> The general problem is that I have a number of sheets from which I want 
> to capture such summary data, but without having to manually edit the 
> cell formulas each time to get them to point to the next sheet.
> 
> Incidentally, the sheet names are NOT sheet1, sheet2, etc. So moving to 
> the next sheet needs to use some general method of incrementing the 
> sheet reference.
> 
> Is there an artful way to do this?

Not 100% sure I understand exactly what you want, but did a test 
macro that does what I think you want. Isn't complex, but mainly a 
process. The Macro uses Page numbers, so it doesn't care what the 
Sheets names are:

Recorded manual process first. Had macro go to cell A30
Then had it copy a1:e1 on second sheet, and go down.
Then copied the lines.
First couple copied the macro lines manually, and modified 
numbers for each arg one by one. Then got smart, and changed 
numbers in blocks of 10 to make it simplier. Just copied the block 
in geany and then changed page number to go to, and then 
changed numbers in next block. Should be obvious.

Only thing might add is to clear the lines on sheet1 since second or 
later times, it gives pop up message since pasting is overwriting 
data. 

sub multisheet2
rem --
rem define variables
dim document   as object
dim dispatcher as object
rem --
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = 
createUnoService("com.sun.star.frame.DispatchHelper")

rem --
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$A$30"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, 
args1())

rem --
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Nr"
args2(0).Value = 2

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, 
args2())

rem --
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$A$1:$E$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, 
args3())

rem --
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem --
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "Nr"
args5(0).Value = 1

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, 
args5())

rem --
dim args6(5) as new com.sun.star.beans.PropertyValue
args6(0).Name = "Flags"
args6(0).Value = "SVDFNT"
args6(1).Name = "FormulaCommand"
args6(1).Value = 0
args6(2).Name = "SkipEmptyCells"
args6(2).Value = false
args6(3).Name = "Transpose"
args6(3).Value = false
args6(4).Name = "AsLink"
args6(4).Value = false
args6(5).Name = "MoveMode"
args6(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, 
args6())

rem --
dim args7(1) as new com.sun.star.beans.PropertyValue
args7(0).Name = "By"
args7(0).Value = 1
args7(1).Name = "Sel"
args7(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, 
args7())

rem --
dim args8(0) as new com.sun.star.beans.PropertyValue
args8(0).Name = "Nr"
args8(0).Value = 3

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, 
args8())

rem --
dim args9(0) a

[libreoffice-users] Calc: how to auto-increment sheet names in a cell formula?

[Tom Cloyd MS MA]

I have trying to solve this problem all evening, with no hint of success:

Let's say I have 11 sheets in a Calc spreadsheet. Sheets 2 to 11 contain 
data summarized in cells A1:E1 of each sheet.


I want to capture these summary data in sheet 1, starting with cell 
A1:E1. But I then want to copy those cells into the next row and have 
the references point to the next SHEET - sheet 3.


The general problem is that I have a number of sheets from which I want 
to capture such summary data, but without having to manually edit the 
cell formulas each time to get them to point to the next sheet.


Incidentally, the sheet names are NOT sheet1, sheet2, etc. So moving to 
the next sheet needs to use some general method of incrementing the 
sheet reference.


Is there an artful way to do this?

Thanks for any ideas offered!

--

~
“Love recognizes no barriers. It jumps hurdles, leaps fences, penetrates walls
to arrive at its destination full of hope.” ~ Maya Angelou
~
Tom Cloyd, MS MA LMHC (WA) | t...@tomcloyd.com
Psychological trauma & dissociative disorders, treatment, research, & advocacy
Spokane, Washington, U.S.A. | (435) 272-3332
https://www.gettraumainfo.com/ (professional)
Facebook: https://www.facebook.com/groups/645665272216298/
www.tomcloyd.com/ (personal)
~
System76 Galago Pro (galp5) laptop
Processors: 8 × 11th Gen Intel® Core™ i7-1165G7 @ 2.80GHz
RAM: 16 GB Dual Channel DDR4 at 3200 MHz
Storage: 500 GIB SSD
Operating System: Pop!_OS 22.04 LTS
Kernel Version: 6.2.6-76060206-generic x86_64
Gnome ver. 42.5
Windowing system: X11
Qt Version: 5.13.3
[updated 2023-05-09:1420]


--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] Calc - how to auto-increment sheet references in a formula?

[Tom Cloyd MS MA]

I have trying to solve this problem all evening, with no hint of success:

Let's say I have 11 sheets in a Calc spreadsheet. Sheets 2 to 11 contain 
data summarized in cells A1:E1 of each sheet.


I want to capture these summary data in sheet 1, starting with cell 
A1:E1. But I then want to copy those cells into the next row and have 
the references point to the next SHEET - sheet 3.


The general problem is that I have a number of sheets from which I want 
to capture such summary data, but without having to manually edit the 
cell formulas each time to get them to point to the next sheet.


Incidentally, the sheet names are NOT sheet1, sheet2, etc. So moving to 
the next sheet needs to use some general method of incrementing the 
sheet reference.


Is there an artful way to do this?

Thanks for any ideas offered!

--

~
“Love recognizes no barriers. It jumps hurdles, leaps fences, penetrates walls
to arrive at its destination full of hope.” ~ Maya Angelou
~
Tom Cloyd, MS MA LMHC (WA) | t...@tomcloyd.com
Psychological trauma & dissociative disorders, treatment, research, & advocacy
Spokane, Washington, U.S.A. | (435) 272-3332
https://www.gettraumainfo.com/ (professional)
Facebook: https://www.facebook.com/groups/645665272216298/
www.tomcloyd.com/ (personal)
~
System76 Galago Pro (galp5) laptop
Processors: 8 × 11th Gen Intel® Core™ i7-1165G7 @ 2.80GHz
RAM: 16 GB Dual Channel DDR4 at 3200 MHz
Storage: 500 GIB SSD
Operating System: Pop!_OS 22.04 LTS
Kernel Version: 6.2.6-76060206-generic x86_64
Gnome ver. 42.5
Windowing system: X11
Qt Version: 5.13.3
[updated 2023-05-09:1420]


--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc - Can't highlight a column and set background shading

[T. R. Valentine]

On Sun, 2 Jul 2023 at 09:47, David Carter  wrote:



> I have previously selected a column and clicked the "background
> highlight" icon with color of yellow selected - that would turn the
> entire column yellow - it no longer works!

No answer, but doing this is a Very Bad Idea.

Spreadsheets to save file size and memory usage, only 'open' sections
of a spreadsheet as each section gets used (you don't want nearly 17.2
*billion* cells [16,384 × 1,048,576] being tracked all the time —
performance would be horribly degraded). Formatting over a million
cells at once opens a *huge* amount of area — are you *really* going
to use over a million rows in your spreadsheet??? — and will degrade
performance.

I understand wanting to highlight cells before usage. But I suspect
highlighting the first few thousand cells in a column will be
sufficient in almost all cases and will result in improved performance
and a smaller file size.


-- 
T. R. Valentine
A rich heart may be under a poor coat.

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc - Can't highlight a column and set background shading

[James]

Jul. 2, 2023 11:33:27 lo.harald.ber...@t-online.de:

Formatting with toolbars is also called direct formatting, and is not 
well suited for larger documents.


To format cells (or columns/rows), cell styles should be used.
How to do that you can find here:
https://wiki.documentfoundation.org/Videos/Cell_Styles_in_Calc


Maybe LO can be smart and create a style when appropriate.

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc - Can't highlight a column and set background shading

[Tom]

On 7/2/23 07:42, David Carter wrote:
Been using Open Office and then LibreOffice since 1993.  Used to use 
Bugzilla occasionally in the past.
. . . This is my first post to this mailing list, to seek support from 
the community.


I have a spreadsheet I've been using for 3 years.  I modify it 
occasionally.


I have previously selected a column and clicked the "background 
highlight" icon with color of yellow selected - that would turn the 
entire column yellow - it no longer works!


I suspect I "thrashed around" and changed something about the Toolbars 
that has caused this problem.


I just uninstalled and re-installed Libre Office on my Windows 7 
Professional desktop computer - LibreOffice 7.5.4.2.  I did this to 
get away from any "unintended" changes I may have made.  It did not 
fix any of the problems noted in the next paragraph.


I also seem to have changed how the toolbars display:  They have the 
"system blue" background color, the icons seem larger or different, 
and the "background highlight" tool seems to be different - has black 
"ab" over yellow line.  See attached screen shot.
*_. . . I can no longer highlight a cell or column of cells and change 
the background to yellow._**_

_**_
_**_This is the problem I am reporting and for which I am asking for 
help._**_

_*
Urgency:  I am "dead in the water" on my number one priority task.



Hi!  I've found Calc 7.5 still works the same as you expect. Make sure 
to have the "Standard Toolbar" displayed (at least to deal with your 
immediate issue) and make sure the "Formatting" toolbar is visible.  
With both of those in place, I was able to do what you describe:


Select a column
Change the background color of the entire column

It was literally two clicks of the mouse.  :)

I'll send you a couple of screenshots since I'm not currently setup to 
host images online.


Peace...

Tom


--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc - Can't highlight a column and set background shading

[DaveB]

Hi David

Can't recall ever seeing a "Highlight" button in Calc, I think you mean 
that you previously added the "Background Color" to the toolbar with the 
"Customise Toolbar" facility.


I can't offer much to help you restore your original settings, other 
than to rename/delete your user profile (last resort) but as an alternative:

1. Click the column header.
2. Right click in the highlighted area to open the context menu.
3. Select "Format cells" from the context menu.
Keyboard alternative to steps 2 & 3 is Ctrl + 1
4. In the "Format Calls" dialog click the "Background" tab.
5. Click the "Color" button and choose the required color.
Obviously click OK.

Hope that helps.

Regards
Dave

 Original Message 
From: David Carter [mailto:dcarter204...@gmail.com]
Sent: Sunday, July 2, 2023, 14:42 UTC
To: LibreOffice User problems, questions
Subject: [libreoffice-users] Calc - Can't highlight a column and set 
background shading


Been using Open Office and then LibreOffice since 1993.  Used to use 
Bugzilla occasionally in the past.
. . . This is my first post to this mailing list, to seek support from 
the community.


I have a spreadsheet I've been using for 3 years.  I modify it 
occasionally.


I have previously selected a column and clicked the "background 
highlight" icon with color of yellow selected - that would turn the 
entire column yellow - it no longer works!


I suspect I "thrashed around" and changed something about the Toolbars 
that has caused this problem.


I just uninstalled and re-installed Libre Office on my Windows 7 
Professional desktop computer - LibreOffice 7.5.4.2.  I did this to get 
away from any "unintended" changes I may have made.  It did not fix any 
of the problems noted in the next paragraph.


I also seem to have changed how the toolbars display:  They have the 
"system blue" background color, the icons seem larger or different, and 
the "background highlight" tool seems to be different - has black "ab" 
over yellow line.  See attached screen shot.
*_. . . I can no longer highlight a cell or column of cells and change 
the background to yellow._**_

_**_
_**_This is the problem I am reporting and for which I am asking for 
help._**_

_*
Urgency:  I am "dead in the water" on my number one priority task.

Best wishes,

David Carter
Temple, Texas
409-718-2268



--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc - Can't highlight a column and set background shading

[LO . Harald . Berger]

Dear David,

a new installation will not touch personal settings.
All settings you have made will be stored in the so-called user profile.

https://wiki.documentfoundation.org/UserProfile

This user profile is not touched during installations.

So if you want to turn a toolbar on or off, choose View>Toolbars from 
the menu.

The toolbar you are looking for is probably "Formatting".

Formatting with toolbars is also called direct formatting, and is not 
well suited for larger documents.


To format cells (or columns/rows), cell styles should be used.
How to do that you can find here:
https://wiki.documentfoundation.org/Videos/Cell_Styles_in_Calc

Good luck!

Best Regards
Harald



Am 02.07.2023 um 16:42 schrieb David Carter:
Been using Open Office and then LibreOffice since 1993.  Used to use 
Bugzilla occasionally in the past.
. . . This is my first post to this mailing list, to seek support from 
the community.


I have a spreadsheet I've been using for 3 years.  I modify it 
occasionally.


I have previously selected a column and clicked the "background 
highlight" icon with color of yellow selected - that would turn the 
entire column yellow - it no longer works!


I suspect I "thrashed around" and changed something about the Toolbars 
that has caused this problem.


I just uninstalled and re-installed Libre Office on my Windows 7 
Professional desktop computer - LibreOffice 7.5.4.2.  I did this to 
get away from any "unintended" changes I may have made.  It did not 
fix any of the problems noted in the next paragraph.


I also seem to have changed how the toolbars display:  They have the 
"system blue" background color, the icons seem larger or different, 
and the "background highlight" tool seems to be different - has black 
"ab" over yellow line.  See attached screen shot.
*_. . . I can no longer highlight a cell or column of cells and change 
the background to yellow._**_

_**_
_**_This is the problem I am reporting and for which I am asking for 
help._**_

_*
Urgency:  I am "dead in the water" on my number one priority task. 



--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] Calc - Can't highlight a column and set background shading

[David Carter]

Been using Open Office and then LibreOffice since 1993.  Used to use 
Bugzilla occasionally in the past.
. . . This is my first post to this mailing list, to seek support from 
the community.


I have a spreadsheet I've been using for 3 years.  I modify it occasionally.

I have previously selected a column and clicked the "background 
highlight" icon with color of yellow selected - that would turn the 
entire column yellow - it no longer works!


I suspect I "thrashed around" and changed something about the Toolbars 
that has caused this problem.


I just uninstalled and re-installed Libre Office on my Windows 7 
Professional desktop computer - LibreOffice 7.5.4.2.  I did this to get 
away from any "unintended" changes I may have made.  It did not fix any 
of the problems noted in the next paragraph.


I also seem to have changed how the toolbars display:  They have the 
"system blue" background color, the icons seem larger or different, and 
the "background highlight" tool seems to be different - has black "ab" 
over yellow line.  See attached screen shot.
*_. . . I can no longer highlight a cell or column of cells and change 
the background to yellow._**_

_**_
_**_This is the problem I am reporting and for which I am asking for 
help._**_

_*
Urgency:  I am "dead in the water" on my number one priority task.

Best wishes,

David Carter
Temple, Texas
409-718-2268

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc default font size

[Philip Jackson]

Hi Mike,

On 22/05/2023 14:10, Mike Flannigan wrote:


Go to Tools > Options > LibreOffice > Fonts and change it there.

Does that work?


I looked there but it seems to be basically a font replacement table informing 
LibreOffice what font to substitute when a document uses a font which is not 
available on the computer. This table only sets the font face and not the size.

Secondly, there is a specification for font for html, sql,  and Basic sources.  
In any case it calls Hack at 15pt which never happens in the spreadsheet. 
Doesn't seem to apply to my case.

Calc always gives me the fontface I want/specify in the Default.ots template 
(Liberation Sans).  My modified template always provides 4 sheets with the font 
size set at the 12pt which I specified. The problem which I have to correct 
manually each time, occurs when I create a new sheet. The font face is correct 
but the size reverts to 10pt on the new sheet.

The same goes if a create a new spreadsheet using File > New > Spreadsheet 
without passing by my Default.ots template. The new spreadsheet has the correct 
number of sheets with the correct font face but all set at 10pt.

So I conclude that LibreOffice has some setting deeper within its structure 
that specifies 10pt.  I would like to understand where that setting is and I'm 
hoping someone can point me to it.

Philip


On 5/22/23 03:08, users+h...@global.libreoffice.org wrote:

Subject:
[libreoffice-users] Calc default font size
From:
Philip Jackson 
Date:
5/21/23, 12:53

To:
LibreOffice 


Using LO 7.3.7.2 in UbuntuStudio 22.04

These days, I need a minimum of  12pt font size in Calc. Creation of a new file via File > 
New > Spreadsheet opens always with settings at 10pt.  I have edited a Default template in 
the .config/libreoffice/ directory with 12pt font size and creating a new file via File > 
New > Templates > Default  does get me a new spreadsheet with settings at 12 pt for the 
first 4 sheets but any extra sheets I create are set at 10pt.

In Tools > Options > LibreOffice Calc > Defaults, there is only an option to specify the 
number of sheets in a new spreadsheet. I've searched under Tools > Options > LibreOffice > 
Advanced > Open Expert Configuration looking for a default font size setting but without success.

Can anyone advise how and where I can adjust the settings so that by default I always 
get new sheets set to 12pt, and preferably by default at the File > New > 
Spreadsheet without having to pass via the template ?

Thanks,
Philip





--
--
<https://le-theron.com>   author of "The Circle of Fifths" for improvising 
musicians
  Get it here https://www.books2read.com/u/bWnRM4

  auteur : "Le Cycle des Quintes" pour musiciens improvisateurs
  disponibilité : https://www.books2read.com/u/bw8rv9
--

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-u

Re: [libreoffice-users] Calc default font size

[Mike Flannigan]

Go to Tools > Options > LibreOffice > Fonts and change it there.

Does that work?


On 5/22/23 03:08, users+h...@global.libreoffice.org wrote:

Subject:
[libreoffice-users] Calc default font size
From:
Philip Jackson 
Date:
5/21/23, 12:53

To:
LibreOffice 


Using LO 7.3.7.2 in UbuntuStudio 22.04

These days, I need a minimum of  12pt font size in Calc. Creation of a 
new file via File > New > Spreadsheet opens always with settings at 
10pt.  I have edited a Default template in the .config/libreoffice/ 
directory with 12pt font size and creating a new file via File > New > 
Templates > Default  does get me a new spreadsheet with settings at 12 
pt for the first 4 sheets but any extra sheets I create are set at 10pt.


In Tools > Options > LibreOffice Calc > Defaults, there is only an 
option to specify the number of sheets in a new spreadsheet. I've 
searched under Tools > Options > LibreOffice > Advanced > Open Expert 
Configuration looking for a default font size setting but without 
success.


Can anyone advise how and where I can adjust the settings so that by 
default I always get new sheets set to 12pt, and preferably by default 
at the File > New > Spreadsheet without having to pass via the template ?


Thanks,
Philip



--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] Calc default font size

[Philip Jackson]

Using LO 7.3.7.2 in UbuntuStudio 22.04

These days, I need a minimum of  12pt font size in Calc. Creation of a new file via File > 
New > Spreadsheet opens always with settings at 10pt.  I have edited a Default template in 
the .config/libreoffice/ directory with 12pt font size and creating a new file via File > 
New > Templates > Default  does get me a new spreadsheet with settings at 12 pt for the 
first 4 sheets but any extra sheets I create are set at 10pt.

In Tools > Options > LibreOffice Calc > Defaults, there is only an option to specify the 
number of sheets in a new spreadsheet. I've searched under Tools > Options > LibreOffice > 
Advanced > Open Expert Configuration looking for a default font size setting but without success.

Can anyone advise how and where I can adjust the settings so that by default I always 
get new sheets set to 12pt, and preferably by default at the File > New > 
Spreadsheet without having to pass via the template ?

Thanks,
Philip

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Fwd: [libreoffice-users] Calc- combine columns data

[Wade Smart]

=concat(a1," ",a2)

So A1 = Mark, A2 = Smith, A3 will be Mark Smith
If A1 is Mike and A2 is empty, A3 will be Mike

--
Registered Linux User: #480675
Registered Linux Machine: #408606
Linux since June 2005

On Fri, Dec 2, 2022 at 7:07 AM .  wrote:
>
> I want to be able to combine the contents of, say, column A into column
> B without losing data in any of the rows.
>
> Each column have blanks in certain rows so the data wont be overwritten.
>
> Example;  A1 has data but B1 doesn't
>
> A2 has no data but B2 does
>
> Can this be done?
>
> Thanks,
>
> Peter
>
>
> --
> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> Problems? 
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc - adding up score numbers

[charles meyer]

I have Excel 365 which operates differently but it finally worked with
Paste Special.

Thank you for your help.

Charles.

On Fri, Oct 21, 2022 at 3:31 PM Hastham S  wrote:

> Hi
>
> Which version of MS XL are you using ?
> Which operating system - MAC or Windows ?
>
> *Did you check the help URL that I had posted*
>
> for example in EXCEL 2003, this is what you do
>
>-
>- *Select the cell(s) whose values should be copied.*
>- *From the Edit menu, select Copy. ... *
>- *Select the first cell where the results should be pasted.*
>- *From the Edit menu, select Paste Special... ... *
>- *Under Paste, select Values.*
>- *Click OK*
>
>
>
> do you see a box like this when you click on paste special ? or what do
> you see?
> [image: Inline image]
>
>
>
> regards
>
>
>
> On Friday, October 21, 2022 at 10:24:14 PM GMT+4, charles meyer <
> reachmepl...@gmail.com> wrote:
>
>
> Hi,
>
> Thanks for the well wishes.
>
> I highlighted the last column with all the total numbers of that judge's
> scoring for that judge.
>
> I then drag the highlighted numbers in each cell in each frew for that
> column down from the first to the last story.
>
> I right clicked and chose Copy.
>
> I then opened a blank worksheet and there are 2 (two) Paste Specials.
>
> I chose the 1st and it doesn't paste in the numbers so I then chose the 2nd
> Paste Special but that didn't paste in the  numbers either.
>
> I'm not sure what's going wrong?
>
> Thanks.
>
> On Fri, Oct 21, 2022 at 1:44 PM Hastham S  wrote:
>
> > Hi Charles
> >
> > Great to know you are safe.
> >
> > Regarding the copy paste, I think you are just doing normal Control + C
> > and Control + V *which will copy the formulae*
> >
> > Please try paste special - paste values, when you paste on to the summary
> > sheet
> >
> > Paste values In MS Excel :
> > Copy and paste specific cell contents
> > <
> https://support.microsoft.com/en-us/office/copy-and-paste-specific-cell-contents-a956b1c3-cd5a-4245-852c-42e8f83ffe71
> >
> >
> > Copy and paste specific cell contents
> >
> >
> > <
> https://support.microsoft.com/en-us/office/copy-and-paste-specific-cell-contents-a956b1c3-cd5a-4245-852c-42e8f83ffe71
> >
> >
> > Paste values In Libre Office calc :
> > Paste Special
> > 
> >
> > Paste Special
> >
> > 
> >
> > Hope this helps
> >
> > regards
> > Hastham / Subu
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > On Friday, October 21, 2022 at 09:10:07 PM GMT+4, charles meyer <
> > reachmepl...@gmail.com> wrote:
> >
> >
> > Hi All,
> >
> >
> >
> > So grateful to have survived Hurricane Ian and to be able to help others
> in
> > the community.
> >
> >
> >
> > It seems Excel isn’t as straight forward as I’d like.
> >
> >
> >
> > Perhaps this is easier to do in Calc?
> >
> >
> >
> > I opened a blank worksheet and thought I’d create the first column of
> rows
> > with each cell populated with the name each a story. I then created
> columns
> > with the heading of the name of each judge.
> >
> >
> >
> > So, 50 rows (each story) and 4 columns comprised of each judge name and
> > then the last column named Total for total of points summed from each of
> > the judge’s scores.
> >
> >
> >
> > When I copy by highlighting all the score numbers from a judge’s total
> > score column and the paste those scores into the 1st cell under the
> column
> > for that judge I get #REF! in each cell.
> >
> >
> >
> > Is there a way to copy all those judge’s scores from each judge’s scoring
> > worksheet and pates them into a column for that judge in a new worksheet
> so
> > I have all the scores from all the judges and in the 4th column I can
> just
> > Click that Z Sum tab to add all 3 scores for each story (and then
> hopefully
> > sort them by highest score to lowest?
> >
> >
> >
> > I’ve never taken any Excel nor Calc class which taught this.
> >
> >
> >
> > Thank you so much.
> >
> >
> >
> > Best,
> >
> >
> >
> > Charles.
> >
> > --
> > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> > Problems?
> > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> > Posting guidelines + more:
> https://wiki.documentfoundation.org/Netiquette
> > List archive: https://listarchives.libreoffice.org/global/users/
> > Privacy Policy: https://www.documentfoundation.org/privacy
>
> >
>
> --
> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> Problems?
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy
>

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.d

Re: [libreoffice-users] Calc - adding up score numbers

[Hastham S]

 Hi
Which version of MS XL are you using ?Which operating system - MAC or Windows ?
Did you check the help URL that I had posted
for example in EXCEL 2003, this is what you do 
   
   -

   - Select the cell(s) whose values should be copied.
   - From the Edit menu, select Copy. ... 
   - Select the first cell where the results should be pasted.
   - From the Edit menu, select Paste Special... ... 
   - Under Paste, select Values.
   - Click OK


do you see a box like this when you click on paste special ? or what do you 
see? 




regards


On Friday, October 21, 2022 at 10:24:14 PM GMT+4, charles meyer 
 wrote:  
 
 Hi,

Thanks for the well wishes.

I highlighted the last column with all the total numbers of that judge's
scoring for that judge.

I then drag the highlighted numbers in each cell in each frew for that
column down from the first to the last story.

I right clicked and chose Copy.

I then opened a blank worksheet and there are 2 (two) Paste Specials.

I chose the 1st and it doesn't paste in the numbers so I then chose the 2nd
Paste Special but that didn't paste in the  numbers either.

I'm not sure what's going wrong?

Thanks.

On Fri, Oct 21, 2022 at 1:44 PM Hastham S  wrote:

> Hi Charles
>
> Great to know you are safe.
>
> Regarding the copy paste, I think you are just doing normal Control + C
> and Control + V *which will copy the formulae*
>
> Please try paste special - paste values, when you paste on to the summary
> sheet
>
> Paste values In MS Excel :
> Copy and paste specific cell contents
> 
>
> Copy and paste specific cell contents
>
>
> 
>
> Paste values In Libre Office calc :
> Paste Special
> 
>
> Paste Special
>
> 
>
> Hope this helps
>
> regards
> Hastham / Subu
>
>
>
>
>
>
>
>
>
>
> On Friday, October 21, 2022 at 09:10:07 PM GMT+4, charles meyer <
> reachmepl...@gmail.com> wrote:
>
>
> Hi All,
>
>
>
> So grateful to have survived Hurricane Ian and to be able to help others in
> the community.
>
>
>
> It seems Excel isn’t as straight forward as I’d like.
>
>
>
> Perhaps this is easier to do in Calc?
>
>
>
> I opened a blank worksheet and thought I’d create the first column of rows
> with each cell populated with the name each a story. I then created columns
> with the heading of the name of each judge.
>
>
>
> So, 50 rows (each story) and 4 columns comprised of each judge name and
> then the last column named Total for total of points summed from each of
> the judge’s scores.
>
>
>
> When I copy by highlighting all the score numbers from a judge’s total
> score column and the paste those scores into the 1st cell under the column
> for that judge I get #REF! in each cell.
>
>
>
> Is there a way to copy all those judge’s scores from each judge’s scoring
> worksheet and pates them into a column for that judge in a new worksheet so
> I have all the scores from all the judges and in the 4th column I can just
> Click that Z Sum tab to add all 3 scores for each story (and then hopefully
> sort them by highest score to lowest?
>
>
>
> I’ve never taken any Excel nor Calc class which taught this.
>
>
>
> Thank you so much.
>
>
>
> Best,
>
>
>
> Charles.
>
> --
> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> Problems?
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy
>

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
  
-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc - adding up score numbers

[charles meyer]

Hi,

Thanks for the well wishes.

I highlighted the last column with all the total numbers of that judge's
scoring for that judge.

I then drag the highlighted numbers in each cell in each frew for that
column down from the first to the last story.

I right clicked and chose Copy.

I then opened a blank worksheet and there are 2 (two) Paste Specials.

I chose the 1st and it doesn't paste in the numbers so I then chose the 2nd
Paste Special but that didn't paste in the  numbers either.

I'm not sure what's going wrong?

Thanks.

On Fri, Oct 21, 2022 at 1:44 PM Hastham S  wrote:

> Hi Charles
>
> Great to know you are safe.
>
> Regarding the copy paste, I think you are just doing normal Control + C
> and Control + V *which will copy the formulae*
>
> Please try paste special - paste values, when you paste on to the summary
> sheet
>
> Paste values In MS Excel :
> Copy and paste specific cell contents
> 
>
> Copy and paste specific cell contents
>
>
> 
>
> Paste values In Libre Office calc :
> Paste Special
> 
>
> Paste Special
>
> 
>
> Hope this helps
>
> regards
> Hastham / Subu
>
>
>
>
>
>
>
>
>
>
> On Friday, October 21, 2022 at 09:10:07 PM GMT+4, charles meyer <
> reachmepl...@gmail.com> wrote:
>
>
> Hi All,
>
>
>
> So grateful to have survived Hurricane Ian and to be able to help others in
> the community.
>
>
>
> It seems Excel isn’t as straight forward as I’d like.
>
>
>
> Perhaps this is easier to do in Calc?
>
>
>
> I opened a blank worksheet and thought I’d create the first column of rows
> with each cell populated with the name each a story. I then created columns
> with the heading of the name of each judge.
>
>
>
> So, 50 rows (each story) and 4 columns comprised of each judge name and
> then the last column named Total for total of points summed from each of
> the judge’s scores.
>
>
>
> When I copy by highlighting all the score numbers from a judge’s total
> score column and the paste those scores into the 1st cell under the column
> for that judge I get #REF! in each cell.
>
>
>
> Is there a way to copy all those judge’s scores from each judge’s scoring
> worksheet and pates them into a column for that judge in a new worksheet so
> I have all the scores from all the judges and in the 4th column I can just
> Click that Z Sum tab to add all 3 scores for each story (and then hopefully
> sort them by highest score to lowest?
>
>
>
> I’ve never taken any Excel nor Calc class which taught this.
>
>
>
> Thank you so much.
>
>
>
> Best,
>
>
>
> Charles.
>
> --
> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> Problems?
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy
>

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc - adding up score numbers

[Hastham S]

 Hi Charles
Great to know you are safe. 

Regarding the copy paste, I think you are just doing normal Control + C and 
Control + V which will copy the formulae
Please try paste special - paste values, when you paste on to the summary sheet

Paste values In MS Excel : 
Copy and paste specific cell contents

| 
| 
|  | 
Copy and paste specific cell contents


 |

 |

 |


Paste values In Libre Office calc : 
Paste Special

| 
| 
|  | 
Paste Special


 |

 |

 |


Hope this helps
regardsHastham / Subu









On Friday, October 21, 2022 at 09:10:07 PM GMT+4, charles meyer 
 wrote:  
 
 Hi All,



So grateful to have survived Hurricane Ian and to be able to help others in
the community.



It seems Excel isn’t as straight forward as I’d like.



Perhaps this is easier to do in Calc?



I opened a blank worksheet and thought I’d create the first column of rows
with each cell populated with the name each a story. I then created columns
with the heading of the name of each judge.



So, 50 rows (each story) and 4 columns comprised of each judge name and
then the last column named Total for total of points summed from each of
the judge’s scores.



When I copy by highlighting all the score numbers from a judge’s total
score column and the paste those scores into the 1st cell under the column
for that judge I get #REF! in each cell.



Is there a way to copy all those judge’s scores from each judge’s scoring
worksheet and pates them into a column for that judge in a new worksheet so
I have all the scores from all the judges and in the 4th column I can just
Click that Z Sum tab to add all 3 scores for each story (and then hopefully
sort them by highest score to lowest?



I’ve never taken any Excel nor Calc class which taught this.



Thank you so much.



Best,



Charles.

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
  
-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] Calc - adding up score numbers

[charles meyer]

Hi All,



So grateful to have survived Hurricane Ian and to be able to help others in
the community.



It seems Excel isn’t as straight forward as I’d like.



Perhaps this is easier to do in Calc?



I opened a blank worksheet and thought I’d create the first column of rows
with each cell populated with the name each a story. I then created columns
with the heading of the name of each judge.



So, 50 rows (each story) and 4 columns comprised of each judge name and
then the last column named Total for total of points summed from each of
the judge’s scores.



When I copy by highlighting all the score numbers from a judge’s total
score column and the paste those scores into the 1st cell under the column
for that judge I get #REF! in each cell.



Is there a way to copy all those judge’s scores from each judge’s scoring
worksheet and pates them into a column for that judge in a new worksheet so
I have all the scores from all the judges and in the 4th column I can just
Click that Z Sum tab to add all 3 scores for each story (and then hopefully
sort them by highest score to lowest?



I’ve never taken any Excel nor Calc class which taught this.



Thank you so much.



Best,



Charles.

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] Calc - selected cell indicator color

[Tom Cloyd MS MA]

When the cursor is placed on a cell in calc, it is outline in light blue 
(in the default theme). I find this miserably hard to see much of the time.


Is there any way to change that color, or better yet to make it always 
contrast with the color of the cell itself?


This would be so helpful to my tired, aging eyes!

Tom

--

~
"Fight for the things that you care about, but do it in a way that will lead
others to join you." ~ Ruth Bader Ginsberg
~
Tom Cloyd, MS MA | t...@tomcloyd.com
Psychological trauma & dissociative disorders research & advocacy
Spokane, Washington, U.S.A. | (435) 272-3332
Facebook: https://www.facebook.com/groups/645665272216298/
www.tomcloyd.com /
~
Hardware: Acer Swift SF314-54
Operating System: Pop!_OS 22.04 LTS
OS Type: 64-bit
Processors: Intel® Core™ i5-8250U CPU @ 1.60GHz × 8
Memory: 8 GiB of RAM
SSD Disk storage: 250 GiB
Graphics: Mesa Intel® UHD Graphics 620 (KBL GT2)
[2022-10-15]


--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] calc formula are displayed as ###

[Alan B]

I see the same effect when the column is too narrow to display the formula.
Display is 

If the column width is increased so the entire formula can be displayed,
then the formula will be displayed.

The fact that the formula does not display over adjacent empty cells, like
text does, I would think of as a required feature. Not calling it a bug
because I don't know and couldn't understand the programming behind the
Display Formulas setting.

The formula() function does not behave the same way and will display the
formula over adjacent cells if they are empty.

On Wed, Jul 13, 2022 at 1:49 AM Uwe Brauer  wrote:

>
>
> Hi
>
> Using LO 7.2, in scalc I selected
>
> Tools-->Options-->Scalc-->View--Display_Formulas
>
> Which I activated
>
> However then the formula in that colum are displayed as ### (before
> turning this option on, the result was displayed)
>
> Is this a BUG or a feature.
>
> If it is a feature I fail to see its benefits.
>
> Regards
>
> Uwe Brauer
>
>
>
>
> --
> I strongly condemn Putin's war of aggression against the Ukraine.
> I support to deliver weapons to Ukraine's military.
> I support the ban of Russia from SWIFT.
> I support the EU membership of the Ukraine.
>
>
> --
> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> Problems?
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy
>
>

-- 
Alan Boba
CISSP, CCENT, ITIL v3 Foundations 2011

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] calc formula are displayed as ###

[Uwe Brauer]

Hi

Using LO 7.2, in scalc I selected

Tools-->Options-->Scalc-->View--Display_Formulas

Which I activated

However then the formula in that colum are displayed as ### (before turning 
this option on, the result was displayed)

Is this a BUG or a feature.

If it is a feature I fail to see its benefits.

Regards

Uwe Brauer




-- 
I strongly condemn Putin's war of aggression against the Ukraine.
I support to deliver weapons to Ukraine's military. 
I support the ban of Russia from SWIFT.
I support the EU membership of the Ukraine. 


-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc function COUNTA

[Mike Flannigan]

That is no surprise to me.
ISBLANK=0
is non-sensical and returns "#NAME?".  COUNTA counts
that as a non-empty value.  All is good.


Mike



On 6/25/22 15:05, users+h...@global.libreoffice.org wrote:

Subject:
[libreoffice-users] Calc function COUNTA
From:
Harvey Nimmo 
Date:
6/25/22, 14:41

To:
libreoffice-users 


I wanted to count the number of non-blank items in a list of items
using COUNTA.

Cell A1 has the formula =COUNTA(A$2:A$1, ISBLANK=0) above an
otherwise empty column.  It returns the value 1!

Is that supposed to mean something? Has anyone notice this?
It's obviously not a big problem, because a workaround is obvious.
But do I have to mistrust the results of the COUNTA function?

Cheers
Harvey



--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc function COUNTA

[Harvey Nimmo]

Thanks for all the inputs! Great stuff.

As I said, I wanted to count the number of non-blank cells in a column,
but as =COUNTA(A$2:A$1, ISBLANK=0) is above an otherwise empty
column returns the value 1, I am obviously not using the function
correctly. =COUNTA(A$2:A$1) does the trick. Thanks.

Cheers
Harvey 




On Sun, 2022-06-26 at 06:40 +0100, Brian Barker wrote:
> At 21:41 25/06/2022 +0200, Harvey Nimmo wrote:
> > I wanted to count the number of non-blank items in a list of items 
> > using COUNTA. Cell A1 has the formula =COUNTA(A$2:A$1, 
> > ISBLANK=0) above an otherwise empty column.  It returns the value
> > 1! 
> > Is that supposed to mean something?
> 
> Yes: it's the correct result!
> 
> The big question is what you intend the formula to mean. ISBLANK()
> is 
> a function, so makes no sense without the parentheses and a 
> parameter. Did you expect that second parameter to COUNTA() to mean
> something?
> 
> What were you actually trying to achieve? Do you want your formula
> to 
> omit cells containing just blanks in its count, as well as empty 
> cells? If so, you need to construct a formula that achieves this.
> But 
> you need to speak Spreadsheet, not just English, to do so! Try
> =SUMPRODUCT(LEN(TRIM(A$2:A$1))>0)
> 
> > It's obviously not a big problem, because a workaround is obvious.
> 
> I don't think it's a problem at all.
> 
> > But do I have to mistrust the results of the COUNTA function?
> 
> No. As others have said, if your specified range is empty, the first 
> parameter contributes zero to the COUNTA() sum. But then the 
> (apparently meaningless) second parameter contributes itself, so the 
> total is indeed one.
> 
> I trust this helps.
> 
> Brian Barker
> 
> 
> 


-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc function COUNTA

[Steve Edmonds]

From the help COUNTA(Number 1 [; Number 2 [; … [; Number 255]]]) 
where*Number 1*, *Number 2*, ..., *Number 255* are numbers, references 
to cells or to cell ranges of numbers.


 * The function counts numbers, dates, error values(Err:502), logical
   values (like TRUE ) or a text representation of numbers (like "7")
   passed either as arguments or a reference to the cells or as an
   array however, empty cells in the reference are not counted.

In your case ISBLANK=0 is seen as Number 2 which is a logical value and 
so is counted as 1.


Steve

On 26/06/2022 10:45, Michael D. Setzer II wrote:

With 3 no blank cells in range
=COUNTBLANK($A$2:$A$1000)
Returns 996
These all return 3
=COUNTA($A$2:$A$1000)
=COUNTIF($A$2:$A$1000,">""")
=COUNTIFS($A$2:$A$1000,">""")

Believe =COUNTA(A$2:A$1, ISBLANK=0) is getting
0 from range, but the counts the isblank=0 as an item?


On 25 Jun 2022 at 21:41, Harvey Nimmo wrote:

Subject:[libreoffice-users] Calc function COUNTA
From:   Harvey Nimmo
To: libreoffice-users

Date sent:  Sat, 25 Jun 2022 21:41:23 +0200


I wanted to count the number of non-blank items in a list of items
using COUNTA.

Cell A1 has the formula =COUNTA(A$2:A$1, ISBLANK=0) above an
otherwise empty column.  It returns the value 1!

Is that supposed to mean something? Has anyone notice this?
It's obviously not a big problem, because a workaround is obvious.
But do I have to mistrust the results of the COUNTA function?

Cheers
Harvey


--
To unsubscribe e-mail to:users+unsubscr...@global.libreoffice.org
Problems?https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more:https://wiki.documentfoundation.org/Netiquette
List archive:https://listarchives.libreoffice.org/global/users/
Privacy Policy:https://www.documentfoundation.org/privacy


++
  Michael D. Setzer II - Computer Science Instructor
(Retired)
  mailto:mi...@guam.net 
  mailto:msetze...@gmail.com

  Guam - Where America's Day Begins
  G4L Disk Imaging Project maintainer
  http://sourceforge.net/projects/g4l/
++






--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc function COUNTA

[Michael D. Setzer II]

With 3 no blank cells in range
=COUNTBLANK($A$2:$A$1000)
Returns 996
These all return 3
=COUNTA($A$2:$A$1000)
=COUNTIF($A$2:$A$1000,">""")
=COUNTIFS($A$2:$A$1000,">""")

Believe =COUNTA(A$2:A$1, ISBLANK=0) is getting 
0 from range, but the counts the isblank=0 as an item?


On 25 Jun 2022 at 21:41, Harvey Nimmo wrote:

Subject:[libreoffice-users] Calc function COUNTA
From:   Harvey Nimmo 
To: libreoffice-users 

Date sent:  Sat, 25 Jun 2022 21:41:23 +0200

> I wanted to count the number of non-blank items in a list of items
> using COUNTA. 
> 
> Cell A1 has the formula =COUNTA(A$2:A$1, ISBLANK=0) above an
> otherwise empty column.  It returns the value 1! 
> 
> Is that supposed to mean something? Has anyone notice this? 
> It's obviously not a big problem, because a workaround is obvious. 
> But do I have to mistrust the results of the COUNTA function?
> 
> Cheers
> Harvey
> 
> 
> -- 
> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> Problems? 
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy


++
 Michael D. Setzer II - Computer Science Instructor 
(Retired) 
 mailto:mi...@guam.net
 mailto:msetze...@gmail.com
 Guam - Where America's Day Begins
 G4L Disk Imaging Project maintainer 
 http://sourceforge.net/projects/g4l/
++




-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] Calc function COUNTA

[Harvey Nimmo]

I wanted to count the number of non-blank items in a list of items
using COUNTA. 

Cell A1 has the formula =COUNTA(A$2:A$1, ISBLANK=0) above an
otherwise empty column.  It returns the value 1! 

Is that supposed to mean something? Has anyone notice this? 
It's obviously not a big problem, because a workaround is obvious. 
But do I have to mistrust the results of the COUNTA function?

Cheers
Harvey


-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] Calc with Form Control not WYSIWYG in Print Preview

[lee lee]

Hello,

Form controls don't look right in print preview in Calc, didn't try in
Writer.  Here are the screenshots:

OS: Mint Linux
Libreoffice: 7.3.3.2

1. Create some controls
[image: image.png]


2. In print preview

a. Zoom : 76% (font size is wrong, label is clipped)
[image: image.png]


b. Zoom : 150% (label missing completely)
[image: image.png]
3. Printed copy and PDF

No issue in printed copy and PDF.

[image: image.png]

Thanks,
David

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc insists on capitalizing m^2 ...

[John Kaufmann]

Brian and Johnny,

On 2022-05-01 08:50, Brian Barker wrote, and
On 2022-05-01 10:52, Johnny Rosenberg wrote:

Den sön 1 maj 2022 kl 14:46 skrev John Kaufmann:


... or any other quantity raised to any other power.  Of course this is
silly in a spreadsheet, where one wants to identify a measurement.

"m" is OK, but "m^2" gets auto-'corrected' to "M^2". Likewise "Cm^2",
"In^2", "Ft^2", "Inch^2", and so forth.

It's embarrassing that I don't see how to turn off this behavior.


Tools → Options for Auto-correction… → Click the Options tab → ☐ Start
every sentence with a capital letter


I /thought/ I had done that (hence the embarrassment). Now I find that I had 
clicked the box above (so the embarrassment is compounded).
[The things one does in the wee hours of the morning ...]


You can also just type m², then you don't need to change any settings. And
it looks better.


I never thought about that! For decades, through many spreadsheet programs, my 
habit has been to treat text cells like formula cells. For me, learning that 
it's not necessary represents a conceptual leap. ;-)  Thanks.

Every day is a school day.  Thanks again to both of you,
John

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc insists on capitalizing m^2 ...

[Johnny Rosenberg]

Den sön 1 maj 2022 kl 14:46 skrev John Kaufmann :

> ... or any other quantity raised to any other power.  Of course this is
> silly in a spreadsheet, where one wants to identify a measurement.
>
> "m" is OK, but "m^2" gets auto-'corrected' to "M^2". Likewise "Cm^2",
> "In^2", "Ft^2", "Inch^2", and so forth.
>
> It's embarrassing that I don't see how to turn off this behavior.
>

Tools → Options for Auto-correction… → Click the Options tab → ☐ Start
every sentence with a capital letter

You can also just type m², then you don't need to change any settings. And
it looks better.



Kind regards

Johnny Rosenberg


> John
>
> --
> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> Problems?
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy
>

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] Calc insists on capitalizing m^2 ...

[John Kaufmann]

... or any other quantity raised to any other power.  Of course this is silly 
in a spreadsheet, where one wants to identify a measurement.

"m" is OK, but "m^2" gets auto-'corrected' to "M^2". Likewise "Cm^2", "In^2", "Ft^2", 
"Inch^2", and so forth.

It's embarrassing that I don't see how to turn off this behavior.

John

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc airithmetic snafu

[bunk3m]

Or perhaps leave the actual number in the first column and just use a 
cell format for the "km".


i.e.

1) Leave 2.4 in the cell
2) ctrl+1 (cmd+1 mac) to open format cell
3) then in the Format Code type " km" behind the number format.

And now your 2.4 will show "2.4 km" in the cell.

And you can do your arithmetical calculations on the cell as you've only 
changed the visual number format.


:-)

B.

On 28.11.2021 14:09, Remy Gauthier wrote:

Greetings,

If you do not like adding columns, you could do the following:

1) Select everything (CTRL-A)
2) Do a search & replace to remove all the "km" indications. This
should also convert the entry in the cells to numbers at the same time.
3) Keeping what is still selected, go to Cell > Format (CTRL-1), and
set the number format to User defined with the format as # ##0.0" km".
This will have your numbers displayed with the proper units.

I hope this helps.
Rémy.

Le dimanche 28 novembre 2021 à 08:37 -0600, Michael H a écrit :

Re: Units out of the data.

1. The formula =Value(Left(C1,Find(" ",C1))) will be insensitive to
longer--129.4 km-- or less specific--7 km-- distances.

2. When I remove units from a list, usually I'm working a large
amount of
data and find that the whole spreadsheet works better when source
data like
this isn't the result of formula. That is, it doesn't take so long to
refresh when I'm only waiting on 10,000 calculations, and not
10,000,000
calculations.

So to avoid those annoying long refresh times, instead of creating
the
source data with formula I copy the column to a new one, ensure there
are
adequate new columns behind it, then convert the column to data with
"Data
-> Text to Columns" and choose to break on space only (in your case,
sometimes I have to list out the first letter of units, but then I
lose the
units, so instead I find/replace the new column to introduce tab
characters
before doing the text to column action. (And if you trust yourself to
recognize mistakes in the text to columns steps before "undo" is
gone,
copying the column to another one isn't required.)

3.

On Fri, Nov 26, 2021 at 11:12 AM Hylton Conacher (ZR1HPC) <
hyl...@conacher.co.za> wrote:


HI ANDREW,

On 2021/11/26 16:38, Andrew Pitonyak wrote:

Left will return a string so convert to a number using value...
Something like this

=Value(left())



THANK YOU Works as expected,now on to the next formula Challenge


Get BlueMail for Android 
On Nov 26, 2021, at 9:12 AM, "Hylton Conacher (ZR1HPC)"
mailto:hyl...@conacher.co.za>> wrote:

     Hi,

     Using LO7.2.2.2    Calc on iMac 11.6.1(Big
Sur).

     I have a data column that ends in alphabetic letters i.e.

     C  |  D
     2| 2.4 km|    2.4
     3| 4.8 km|    4.8

     As the ' km' can't be added in column C, I used the LEFT
formula to

only

     extract the first 3 digits, namely =LEFT(C2,3). This works

wonderfully

     in column D, BUT whilst not shown on the spreadsheet, the
numbers are
     shown in-between double quotes if one looks at the formula,
in

column D,

     with the Function Wizard, which again does not enable the
number to

be

     added, averaged etc.

     Both C, D columns are formatted as Number with a single
decimal point
     and there will be no -ve number.

     What am I missing, and where to rectify it?

     Regards
     Hylton




--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems?
https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more:
https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy








--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc airithmetic snafu

[Michael H]

Re: Units out of the data.

1. The formula =Value(Left(C1,Find(" ",C1))) will be insensitive to
longer--129.4 km-- or less specific--7 km-- distances.

2. When I remove units from a list, usually I'm working a large amount of
data and find that the whole spreadsheet works better when source data like
this isn't the result of formula. That is, it doesn't take so long to
refresh when I'm only waiting on 10,000 calculations, and not 10,000,000
calculations.

So to avoid those annoying long refresh times, instead of creating the
source data with formula I copy the column to a new one, ensure there are
adequate new columns behind it, then convert the column to data with "Data
-> Text to Columns" and choose to break on space only (in your case,
sometimes I have to list out the first letter of units, but then I lose the
units, so instead I find/replace the new column to introduce tab characters
before doing the text to column action. (And if you trust yourself to
recognize mistakes in the text to columns steps before "undo" is gone,
copying the column to another one isn't required.)

3.

On Fri, Nov 26, 2021 at 11:12 AM Hylton Conacher (ZR1HPC) <
hyl...@conacher.co.za> wrote:

> HI ANDREW,
>
> On 2021/11/26 16:38, Andrew Pitonyak wrote:
> > Left will return a string so convert to a number using value...
> > Something like this
> >
> > =Value(left())
> >
>
> THANK YOU Works as expected,now on to the next formula Challenge
>
> > Get BlueMail for Android 
> > On Nov 26, 2021, at 9:12 AM, "Hylton Conacher (ZR1HPC)"
> > mailto:hyl...@conacher.co.za>> wrote:
> >
> > Hi,
> >
> > Using LO7.2.2.2    Calc on iMac 11.6.1(Big Sur).
> >
> > I have a data column that ends in alphabetic letters i.e.
> >
> > C  |  D
> > 2| 2.4 km|2.4
> > 3| 4.8 km|4.8
> >
> > As the ' km' can't be added in column C, I used the LEFT formula to
> only
> > extract the first 3 digits, namely =LEFT(C2,3). This works
> wonderfully
> > in column D, BUT whilst not shown on the spreadsheet, the numbers are
> > shown in-between double quotes if one looks at the formula, in
> column D,
> > with the Function Wizard, which again does not enable the number to
> be
> > added, averaged etc.
> >
> > Both C, D columns are formatted as Number with a single decimal point
> > and there will be no -ve number.
> >
> > What am I missing, and where to rectify it?
> >
> > Regards
> > Hylton
> >
>
>
> --
> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> Problems?
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy
>

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc airithmetic snafu

[Hylton Conacher (ZR1HPC)]

HI ANDREW,

On 2021/11/26 16:38, Andrew Pitonyak wrote:
Left will return a string so convert to a number using value... 
Something like this


=Value(left())



THANK YOU Works as expected,now on to the next formula Challenge


Get BlueMail for Android 
On Nov 26, 2021, at 9:12 AM, "Hylton Conacher (ZR1HPC)" 
mailto:hyl...@conacher.co.za>> wrote:


Hi,

Using LO7.2.2.2    Calc on iMac 11.6.1(Big Sur).

I have a data column that ends in alphabetic letters i.e.

    C      |  D
2| 2.4 km|    2.4
3| 4.8 km|    4.8

As the ' km' can't be added in column C, I used the LEFT formula to only
extract the first 3 digits, namely =LEFT(C2,3). This works wonderfully
in column D, BUT whilst not shown on the spreadsheet, the numbers are
shown in-between double quotes if one looks at the formula, in column D,
with the Function Wizard, which again does not enable the number to be
added, averaged etc.

Both C, D columns are formatted as Number with a single decimal point
and there will be no -ve number.

What am I missing, and where to rectify it?

Regards
Hylton




--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc airithmetic snafu

[Andrew Pitonyak]

Left will return a string so convert to a number using value... Something like 
this

=Value(left())

⁣Get BlueMail for Android ​

On Nov 26, 2021, 9:12 AM, at 9:12 AM, "Hylton Conacher (ZR1HPC)" 
 wrote:
>Hi,
>
>Using LO 7.2.2.2 Calc on iMac 11.6.1(Big Sur).
>
>I have a data column that ends in alphabetic letters i.e.
>
>   C      |  D
>2| 2.4 km|    2.4
>3| 4.8 km|    4.8
>
>As the ' km' can't be added in column C, I used the LEFT formula to
>only 
>extract the first 3 digits, namely =LEFT(C2,3). This works wonderfully 
>in column D, BUT whilst not shown on the spreadsheet, the numbers are 
>shown in-between double quotes if one looks at the formula, in column
>D, 
>with the Function Wizard, which again does not enable the number to be 
>added, averaged etc.
>
>Both C, D columns are formatted as Number with a single decimal point 
>and there will be no -ve number.
>
>What am I missing, and where to rectify it?
>
>Regards
>Hylton
>
>-- 
>To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
>Problems?
>https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
>Posting guidelines + more:
>https://wiki.documentfoundation.org/Netiquette
>List archive: https://listarchives.libreoffice.org/global/users/
>Privacy Policy: https://www.documentfoundation.org/privacy

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] Calc airithmetic snafu

[Hylton Conacher (ZR1HPC)]

Hi,

Using LO 7.2.2.2 Calc on iMac 11.6.1(Big Sur).

I have a data column that ends in alphabetic letters i.e.

  C      |  D
2| 2.4 km|    2.4
3| 4.8 km|    4.8

As the ' km' can't be added in column C, I used the LEFT formula to only 
extract the first 3 digits, namely =LEFT(C2,3). This works wonderfully 
in column D, BUT whilst not shown on the spreadsheet, the numbers are 
shown in-between double quotes if one looks at the formula, in column D, 
with the Function Wizard, which again does not enable the number to be 
added, averaged etc.


Both C, D columns are formatted as Number with a single decimal point 
and there will be no -ve number.


What am I missing, and where to rectify it?

Regards
Hylton

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] calc charting issue

[Dave Stevens]

On Sun, 01 Aug 2021 00:02:50 +0100
Brian Barker  wrote:

> I trust this helps.
> 
> Brian Barker

yes it did, I've double checked and now get the results I want. Thanks
very much!

d

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] calc charting issue

[Brian Barker]

At 15:18 31/07/2021 -0700, Dave Stevens wrote:
I have a small spreadsheet 5x12 elements and the numeric content 
data is missing for some cells. Their contents are given as ND for 
No Data. I want to chart these 5 rows and at first thought I'd just 
set them to zero. The range on y-axis values is about from 5 to 15. 
But with zeros the chart line takes a dive to the x-axis then back up.


I think a better visualisation would be for the graph lines to be 
discontinuous where no data exists, precluding zeroing or smoothing 
over the gap (usually only one missing datum) I don't see a handy way
to do this, does someone with more experience see how? I'll read 
instructions if pointed to them.


Yes: if you set your cells to zero, they will indeed be taken for 
zero and plotted as such. You need to have your missing data cells 
genuinely empty. I'm surprised if that doesn't give you what you 
require by default. If not, right-click on one of the points in a 
relevant line in the chart and select Format Data Series... | Options 
| Plot Options. Set "Plot missing values" to "Leave gap" (or as preferred).


I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] calc charting issue

[Dave Stevens]

I have a small spreadsheet 5x12 elements and the numeric content data
is missing for some cells. Their contents are given as ND for No Data. I
want to chart these 5 rows and at first thought I'd just set them to
zero. The range on y-axis values is about from 5 to 15. But with zeros
the chart line takes a dive to the x-axis then back up. 

I think a better visualisation would be for the graph lines to be
discontinuous where no data exists, precluding zeroing or smoothing
over the gap (usually only one missing datum) I don't see a handy way
to do this, does someone with more experience see how? I'll read
instructions if pointed to them.

Thanks,

Dave

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc: Adapting row height is slow

[V Stuart Foote]

The other way is to open the ODF archive Zip and directly (e.g. with 7-zip) and 
edit the 'content.xml' file (gvim works well when set as the Editor for 7-zip).

Find and change the 'use-optimal-row-height="true"' to "false"; there should 
only be a couple of these style settings. Save the changed contentl.xml file 
back into the Zip archive.

A little more tricky, to open the archive and edit the XML directly, but less 
potential to make other changes to ODS Sheet formatting done like this.



-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc: Adapting row height is slow

[V Stuart Foote]

That is issue of 
tdf#124098 and 
caused by tdf#62268
 see comments at end of bug 62268.

Simple workaround is to get the sheet open and then remove the optimal row 
height by selecting all cells and setting a row height from Format -> Rows -> 
Height.

It will tweak the layout of the sheet but will restore reasonable opening speed.


From: David Lynch
Sent: Friday, July 30, 2021 4:04 AM

I have a large (10 by 100) but simple spreadsheet.

When loading and, sometimes, editing it appears to take longer "Adapting
row height" than the rest of the processing put together. I am happy
with the existing row height and do not want it adapted.

Is there anything I can do to eliminate or speed up this part of the
processing?

David Lynch

Version: 7.1.4.2 (x64) / LibreOffice Community
Build ID: a529a4fab45b75fefc5b6226684193eb000654f6
CPU threads: 4; OS: Windows 10.0 Build 19041; UI render: Skia/Raster;
VCL: win
Locale: en-GB (en_GB); UI: en-GB
Calc: threaded



-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] Calc: Adapting row height is slow

[David Lynch]

I have a large (10 by 100) but simple spreadsheet.

When loading and, sometimes, editing it appears to take longer "Adapting 
row height" than the rest of the processing put together. I am happy 
with the existing row height and do not want it adapted.


Is there anything I can do to eliminate or speed up this part of the 
processing?


David Lynch

Version: 7.1.4.2 (x64) / LibreOffice Community
Build ID: a529a4fab45b75fefc5b6226684193eb000654f6
CPU threads: 4; OS: Windows 10.0 Build 19041; UI render: Skia/Raster; 
VCL: win

Locale: en-GB (en_GB); UI: en-GB
Calc: threaded



--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] Calc file "invalid parameter"

[Thomas Blasejewicz]

Good evening

Again some strange question.


Recently LibreOffice, maybe the computer?, has started to act strange on 
some occasions. Checking the net, I found only ONE related item.


https://forum.openoffice.org/en/forum/viewtopic.php?f=11&t=96478

This is not really a "problem", but I would still like to know, what is 
going on.



I have a sort of "cloud storage" called pCloud, which creates a 5-GB 
virtual drive with its own drive letter on my HDD. There I store among 
other stuff files I use regularly and update several times a day. For 
some time now, a number of Calc files open alright, but when I edit them 
and hit "save", I get the following error message:


"Error saving the document "file name".

The operation [path / file name] was started with an invalid parameter."


When I rename the file, I can save it, but the same problem tends to 
recur when I open it the next time.


When I rename it and store it in a different location, the problem seems 
to disappear.


Probably all I have to do, is choose a different folder to save those files.

Yet, I would really like to know, what that "invalid parameter" refers to.


If anyone has any idea, I would love to hear it.

Thank you.

Thomas



--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc how to display Autofilter values?

[Mike Flannigan]

Ahh.  Now I understand.  I can see how that might be useful.
Sorry, but I don't know a way to do that right now.
Seems like that might be a relatively easy thing to do,
but I don't know how.


Mike


On 6/9/21 8:24 PM, Alan B wrote:
Not at all what I'm trying to do. Distinct values have their uses. But 
I already knew how to do that.


I'm trying to display the AutoFilter values being applied to a table 
so they can be read without opening up each filter at the top of each 
column to see what the settings are.


For example, three columns Name, Weight, Dimensions. The table has 
1000 rows. AutoFilter is in use and 727 rows are displayed. What are 
the filter settings? Sure, can tell if a filter is applied by looking 
at the filter list widget and opening it if it shows the filter is in 
use. And even if all three are in use, no big deal. Only three to open 
and check the values.


When the table has 30+ columns and hundreds or thousands of rows, not 
such a simple task. Much more convenient just to have a formula that 
will display the settings of all applied filters.


If I'm not explaining the requirement clearly please look at the link 
to the Excel solution. That's got pictures along with an explanation 
of the problem and solution.


That's what I'd like to reproduce in Calc.



On Wed, Jun 9, 2021 at 6:49 AM Mike Flannigan > wrote:



See this:

https://bytefreaks.net/applications/libreoffice-calc-get-the-distinct-unique-values-in-a-column-or-a-list-of-elements

You can use Ctrl-z to undo the command, but don't use Ctrl-Shift-y
to redo it
on another column.  You must go through the command again on
another column
and then do Ctrl-z again.


Mike


On 6/9/21 3:07 AM, users+h...@global.libreoffice.org
 wrote:

Many moons ago I was using some Calc sheets with AutoFilter routinely. The
filter value would be changed, the sheet closed and sometime later opened
again.

To understand the displayed rows the filter values needed to be known. And
that involved opening each filter and checking the value.

So I asked, is there a way to display those values? No answers. And the
need went away.

Just recently had the same need in Excel. Found online the exact answer
needed to make it work.

https://www.extendoffice.com/documents/excel/1444-excel-display-show-filter-criteria.html
The section "Display / Show auto filter criteria in worksheet with VBA
code" is what I referenced.

I've used that info to try and extrapolate a similar method in Calc to no
avail.

Have done a limited amount of searching in Andrew Pitonyak's OpenOffice.org
Macros Explained book and searched through OASIS Open Document Format
documents using terms like "filter" and "range". The answer might have been
staring me in the face, I'm thinking in particular of section 15.6.3 of
Andrew Pitonyak's OpenOffice.org Macros Explained, but I just didn't
understand it.

Can anyone provide guidance to display the autofilter values in a Calc
sheet equivalent to the instructions provided for Excel at the link above?

Thank you.




--
Alan Boba
CISSP, CCENT, ITIL v3 Foundations 2011




--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc how to display Autofilter values?

[Alan B]

Not at all what I'm trying to do. Distinct values have their uses. But I
already knew how to do that.

I'm trying to display the AutoFilter values being applied to a table so
they can be read without opening up each filter at the top of each column
to see what the settings are.

For example, three columns Name, Weight, Dimensions. The table has 1000
rows. AutoFilter is in use and 727 rows are displayed. What are the filter
settings? Sure, can tell if a filter is applied by looking at the filter
list widget and opening it if it shows the filter is in use. And even if
all three are in use, no big deal. Only three to open and check the values.

When the table has 30+ columns and hundreds or thousands of rows, not such
a simple task. Much more convenient just to have a formula that will
display the settings of all applied filters.

If I'm not explaining the requirement clearly please look at the link to
the Excel solution. That's got pictures along with an explanation of the
problem and solution.

That's what I'd like to reproduce in Calc.



On Wed, Jun 9, 2021 at 6:49 AM Mike Flannigan  wrote:

>
> See this:
>
> https://bytefreaks.net/applications/libreoffice-calc-get-the-distinct-unique-values-in-a-column-or-a-list-of-elements
>
> You can use Ctrl-z to undo the command, but don't use Ctrl-Shift-y to redo
> it
> on another column.  You must go through the command again on another
> column
> and then do Ctrl-z again.
>
>
> Mike
>
>
> On 6/9/21 3:07 AM, users+h...@global.libreoffice.org wrote:
>
> Many moons ago I was using some Calc sheets with AutoFilter routinely. The
> filter value would be changed, the sheet closed and sometime later opened
> again.
>
> To understand the displayed rows the filter values needed to be known. And
> that involved opening each filter and checking the value.
>
> So I asked, is there a way to display those values? No answers. And the
> need went away.
>
> Just recently had the same need in Excel. Found online the exact answer
> needed to make it 
> work.https://www.extendoffice.com/documents/excel/1444-excel-display-show-filter-criteria.html
> The section "Display / Show auto filter criteria in worksheet with VBA
> code" is what I referenced.
>
> I've used that info to try and extrapolate a similar method in Calc to no
> avail.
>
> Have done a limited amount of searching in Andrew Pitonyak's OpenOffice.org
> Macros Explained book and searched through OASIS Open Document Format
> documents using terms like "filter" and "range". The answer might have been
> staring me in the face, I'm thinking in particular of section 15.6.3 of
> Andrew Pitonyak's OpenOffice.org Macros Explained, but I just didn't
> understand it.
>
> Can anyone provide guidance to display the autofilter values in a Calc
> sheet equivalent to the instructions provided for Excel at the link above?
>
> Thank you.
>
>
>

-- 
Alan Boba
CISSP, CCENT, ITIL v3 Foundations 2011

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc how to display Autofilter values?

[Mike Flannigan]

See this:
https://bytefreaks.net/applications/libreoffice-calc-get-the-distinct-unique-values-in-a-column-or-a-list-of-elements

You can use Ctrl-z to undo the command, but don't use Ctrl-Shift-y to 
redo it

on another column.  You must go through the command again on another column
and then do Ctrl-z again.


Mike


On 6/9/21 3:07 AM, users+h...@global.libreoffice.org wrote:

Many moons ago I was using some Calc sheets with AutoFilter routinely. The
filter value would be changed, the sheet closed and sometime later opened
again.

To understand the displayed rows the filter values needed to be known. And
that involved opening each filter and checking the value.

So I asked, is there a way to display those values? No answers. And the
need went away.

Just recently had the same need in Excel. Found online the exact answer
needed to make it work.
https://www.extendoffice.com/documents/excel/1444-excel-display-show-filter-criteria.html
The section "Display / Show auto filter criteria in worksheet with VBA
code" is what I referenced.

I've used that info to try and extrapolate a similar method in Calc to no
avail.

Have done a limited amount of searching in Andrew Pitonyak's OpenOffice.org
Macros Explained book and searched through OASIS Open Document Format
documents using terms like "filter" and "range". The answer might have been
staring me in the face, I'm thinking in particular of section 15.6.3 of
Andrew Pitonyak's OpenOffice.org Macros Explained, but I just didn't
understand it.

Can anyone provide guidance to display the autofilter values in a Calc
sheet equivalent to the instructions provided for Excel at the link above?

Thank you.



--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] Calc how to display Autofilter values? Not answered, maybe insight from Excel elicits answer

[Alan B]

Many moons ago I was using some Calc sheets with AutoFilter routinely. The
filter value would be changed, the sheet closed and sometime later opened
again.

To understand the displayed rows the filter values needed to be known. And
that involved opening each filter and checking the value.

So I asked, is there a way to display those values? No answers. And the
need went away.

Just recently had the same need in Excel. Found online the exact answer
needed to make it work.
https://www.extendoffice.com/documents/excel/1444-excel-display-show-filter-criteria.html
The section "Display / Show auto filter criteria in worksheet with VBA
code" is what I referenced.

I've used that info to try and extrapolate a similar method in Calc to no
avail.

Have done a limited amount of searching in Andrew Pitonyak's OpenOffice.org
Macros Explained book and searched through OASIS Open Document Format
documents using terms like "filter" and "range". The answer might have been
staring me in the face, I'm thinking in particular of section 15.6.3 of
Andrew Pitonyak's OpenOffice.org Macros Explained, but I just didn't
understand it.

Can anyone provide guidance to display the autofilter values in a Calc
sheet equivalent to the instructions provided for Excel at the link above?

Thank you.

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc Formula confusion an understanding needed Index/Match

[Johnny Rosenberg]

Den sön 30 maj 2021 kl 18:05 skrev Hylton Conacher (ZR1HPC) <
hyl...@conacher.co.za>:

> On 2021/05/29 23:36, Johnny Rosenberg wrote:
> > Den lör 29 maj 2021 kl 21:58 skrev Hylton Conacher (ZR1HPC) <
> > hyl...@conacher.co.za>:
> >
> >> Hi Johnny,
> >>
> >> On 2021/05/28 23:44, Johnny Rosenberg wrote:
> >>> Den fre 28 maj 2021 kl 22:49 skrev Hylton Conacher (ZR1HPC) <
> >>> hyl...@conacher.co.za>:
> >>>
>  Hi,
> 
>  I have LO 7.0.6.2 and am battling with understanding which formula to
>  use as well as the syntax for that formula.
> 
>  I am aware of the availability of vlookup, hlookup, Index/Match
> formula
>  and have settled I think on the right one i.e. Index/Match
> 
>  Below is a portion of my spreadsheet that is divided as below with a
>  blank column between each year. What I want to calculate is the date
> the
>  Max rain occurred. I am OK with the formula to obtain the MAX but I
> need
>  help in constructing a formula to get the corresponding date.
> 
>  I had though the best would be Index and Match but no matter how I
> enter
>  it I cannot get the date listed under the Date column of 2020 or 2021,
>  never mind actually retrieving the year from the same column as the
> date
>  the originated.
> 
>  =INDEX(a1:h14;MATCH(h3;a3:h3;0)) just gives me 01/01 for the first
> date
>  under Highest Monthly
> 
> >>>
> >>> Strange. I give a completely different result, so obviously my
> >> spreadsheet
> >>> isn't identical to yours.
> >>> In which cell did you put this formula and what result did you expect?
> >>>
> 
>  I have looked aver tutorial and their mothers trying to find out what
>  ranges to insert into the Index(Match()) formula with ZERO success.
> 
>  I am manually entering the dates under each year when the max value is
>  revealed by my formula. Would love to have it automated but my entire
>  spreadsheet covers over 400 rows and more than 52 columns resulting in
>  26 tabs of graphs from the Data sheet.
> 
>  Is my data in the wrong order i.e. should the rainfall value column be
>  before the Date it occurred?
> 
>  I do not understand what ranges need to consist of when using
> >> Index/Match.
> 
>  Can someone point me to a decent tutorial explaining the different
> terms
>  i.e. Reference, Row, Column, Range, Search Criterion, Lookup array.
> 
>  20202021Highest Monthly
>  Date2020 Rain   Date2021 Rain   DateRain
>  01/19   9,5 01/15   3   2020/01/19  9,5
>  02/16   1,5 02/14   3,5 2021/02/14  3,5
>  03/25   3,5 03/14   19  2021/03/14  19
>  04/11   20  04/26   7   2020/04/11  20
>  05/28   27,505/20   43  2021/05/20  43
>  06/11   26  #N/A0   0
>  07/09   85,5#N/A0   0
>  08/28   35  #N/A0   0
>  09/02   21  #N/A0   0
>  10/28   15  #N/A0   0
>  11/06   25  #N/A0   0
>  12/26   2   #N/A0   0
> 
>  If you want the entire spreadsheet it is available on direct request,
>  but ultimately I would like to understand how it works.
> 
>  This will at least enable to use the formula successfully on newer
>  versions on LO.
> 
>  Regards
>  Hylton
> 
> 
> >>> I'm not sure what you are trying to do here, so I'll just guess. Just
> >>> ignore me if I'm totally wrong.
> >>> So first, I tried to create a spreadsheet following the text above, so
> >> this
> >>> is what my spreadsheet looks like:
> >>> Row 1 and two are just headers.
> >>> Column A is dates for 2020 formatted as Month/Day.
> >>> Column B is amount of rain in some unit, not sure which one, so I
> assume
> >>> mm, since that's what we use where I live, and it doesn't matter for
> this
> >>> question anyway.
> >>> Column C is empty.
> >>> Column D is dates for 2021 formatted as Month/Day.
> >>> Column E is the amount of rain for 2021.
> >>> Column F is empty.
> >>> Column G is the column that contains the dates for each month for the
> >>> respective year with the highest amount of rain, and it's also the
> column
> >>> that you wish to automate, is that right?
> >>> Column H is the highest value of rain in columns B and E for each row.
> >>>
> >>> Right so far?
> >>>
> >>> If so, you want column G to display the date for each maximum value in
> >> the
> >>> B and E column per row, right? Column H  is already figured out, so for
> >>> instance, H3 contains the following:
> >>> =MAX(B

Re: [libreoffice-users] Calc Formula confusion an understanding needed Index/Match

[Hylton Conacher (ZR1HPC)]

Bravo Remy,

On 2021/05/29 20:29, Remy Gauthier wrote:

Hello,

I created something that ressembles what Johnny created, and I 
understand your data is formatted like so:


Data1 Data2 blank Data3 Data4 blank Data5 Data6

You want Data6 to be the maximum of Data2 and Data4 (and possibly more 
columns as well), and you want to have Data5 equal to the date on which 
the maximum occurred.


As Johnny indicated, the formula to place in Data6 is =MAX(Bx;Ex) where 
x is the row number.


If you want to use MATCH to find the date, then you shoud proceed like so:

The MATCH() function takes 3 arguments:
- Value to search
- Array where to search
- How to search
This function will return where the value you are looking for is located 
in the search array. The "how to search" argument tells the function if 
the values in the array are sorted (1 or -1) or not (0). In your case, 
the values are not sorted so you will need to use 0. Note that if you 
use 1 or -1, the search will return the closest match,and will not fail 
if the value you are looking for does not exist in your search array; if 
you use 0, however, the search will fail if your value is not in the 
array. I will usually always use zero (exact match) regardless of how 
the data is sorted. The only time I will make use of the 1 or -1 values 
is when I need to interpolate in a series of data points and I need to 
find where the interpolation will take place in the dataset.


The first argument will be Data6. The second argument will be Ax:Ex, 
where x is the row number. You can use the entire row like this because:

- Date values will always be greater than the rain values you have
- Empty cells do not count
The third argument will be zero since you want an exact match in an 
array that is not sorted.


The output of the MATCH() function will be the column number of the 
maximum (since the first cell of the search range is Ax). The date is 1 
to the left: one column less, so MATCH()-1 will give the column where we 
can find the date.


To extract the date with INDEX(), you must use the same range as used to 
MATCH() the value. The arguments are:

- Cell range
- Row in the range (1 if you only select one row of data)
- Column in the range

The cell range will be Ax:Ex (exactly what was used in the MATCH() 
function), the row will be 1 (only 1 row of data), and the column will 
be the result of the MATCH() we did minus 1. This means the formula in 
Gx will be:


=INDEX(Ax:Ex;1;MATCH(Hx;Ax:Ex;0)-1)

I hope this helps.
Rémy.


Works like a charm.  I mostly understood your explanation too. and will 
keep reviewing it until it sinks in


I copy and pasted your formula and then 'reinvented' it to cover the 
correct ranges and lines in the Data sheet.


Now the job is to incorporate the year into the date.

Regards
Hylton

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc Formula confusion an understanding needed Index/Match

[Hylton Conacher (ZR1HPC)]

On 2021/05/29 23:36, Johnny Rosenberg wrote:

Den lör 29 maj 2021 kl 21:58 skrev Hylton Conacher (ZR1HPC) <
hyl...@conacher.co.za>:


Hi Johnny,

On 2021/05/28 23:44, Johnny Rosenberg wrote:

Den fre 28 maj 2021 kl 22:49 skrev Hylton Conacher (ZR1HPC) <
hyl...@conacher.co.za>:


Hi,

I have LO 7.0.6.2 and am battling with understanding which formula to
use as well as the syntax for that formula.

I am aware of the availability of vlookup, hlookup, Index/Match formula
and have settled I think on the right one i.e. Index/Match

Below is a portion of my spreadsheet that is divided as below with a
blank column between each year. What I want to calculate is the date the
Max rain occurred. I am OK with the formula to obtain the MAX but I need
help in constructing a formula to get the corresponding date.

I had though the best would be Index and Match but no matter how I enter
it I cannot get the date listed under the Date column of 2020 or 2021,
never mind actually retrieving the year from the same column as the date
the originated.

=INDEX(a1:h14;MATCH(h3;a3:h3;0)) just gives me 01/01 for the first date
under Highest Monthly



Strange. I give a completely different result, so obviously my

spreadsheet

isn't identical to yours.
In which cell did you put this formula and what result did you expect?



I have looked aver tutorial and their mothers trying to find out what
ranges to insert into the Index(Match()) formula with ZERO success.

I am manually entering the dates under each year when the max value is
revealed by my formula. Would love to have it automated but my entire
spreadsheet covers over 400 rows and more than 52 columns resulting in
26 tabs of graphs from the Data sheet.

Is my data in the wrong order i.e. should the rainfall value column be
before the Date it occurred?

I do not understand what ranges need to consist of when using

Index/Match.


Can someone point me to a decent tutorial explaining the different terms
i.e. Reference, Row, Column, Range, Search Criterion, Lookup array.

20202021Highest Monthly
Date2020 Rain   Date2021 Rain   DateRain
01/19   9,5 01/15   3   2020/01/19  9,5
02/16   1,5 02/14   3,5 2021/02/14  3,5
03/25   3,5 03/14   19  2021/03/14  19
04/11   20  04/26   7   2020/04/11  20
05/28   27,505/20   43  2021/05/20  43
06/11   26  #N/A0   0
07/09   85,5#N/A0   0
08/28   35  #N/A0   0
09/02   21  #N/A0   0
10/28   15  #N/A0   0
11/06   25  #N/A0   0
12/26   2   #N/A0   0

If you want the entire spreadsheet it is available on direct request,
but ultimately I would like to understand how it works.

This will at least enable to use the formula successfully on newer
versions on LO.

Regards
Hylton



I'm not sure what you are trying to do here, so I'll just guess. Just
ignore me if I'm totally wrong.
So first, I tried to create a spreadsheet following the text above, so

this

is what my spreadsheet looks like:
Row 1 and two are just headers.
Column A is dates for 2020 formatted as Month/Day.
Column B is amount of rain in some unit, not sure which one, so I assume
mm, since that's what we use where I live, and it doesn't matter for this
question anyway.
Column C is empty.
Column D is dates for 2021 formatted as Month/Day.
Column E is the amount of rain for 2021.
Column F is empty.
Column G is the column that contains the dates for each month for the
respective year with the highest amount of rain, and it's also the column
that you wish to automate, is that right?
Column H is the highest value of rain in columns B and E for each row.

Right so far?

If so, you want column G to display the date for each maximum value in

the

B and E column per row, right? Column H  is already figured out, so for
instance, H3 contains the following:
=MAX(B3;E3)

Then you want to automatically display the datum of which this occured,

or

just the year? Well, date or year is only a question about formatting, so
let's just leave it to be formatted later. I guess you know how to do

cell

formats and styles anyway.
maybe I totally misunderstood the question, but if not, you don't need
neither INDEX nor MATCH for this. Here's my cell formula in G3, for
instance:
=IF(B3>E3;A3;D3) <
So it there was more rain in 2020 than in 2021, display the date in

column

A, otherwise display the date in column D.
If you want to fill further down to future dates and only show the values
when the rest of the row is completely entered, you could add another

test,

like this:
=IF(OR(A3=""

Re: [libreoffice-users] Calc Formula confusion an understanding needed Index/Match

[Remy Gauthier]

Hello,
I created something that ressembles what Johnny created, and I
understand your data is formatted like so:
Data1 Data2 blank Data3 Data4 blank Data5 Data6
You want Data6 to be the maximum of Data2 and Data4 (and possibly more
columns as well), and you want to have Data5 equal to the date on which
the maximum occurred.
As Johnny indicated, the formula to place in Data6 is =MAX(Bx;Ex) where
x is the row number.
If you want to use MATCH to find the date, then you shoud proceed like
so:
The MATCH() function takes 3 arguments:- Value to search- Array where
to search- How to searchThis function will return where the value you
are looking for is located in the search array. The "how to search"
argument tells the function if the values in the array are sorted (1 or
-1) or not (0). In your case, the values are not sorted so you will
need to use 0. Note that if you use 1 or -1, the search will return the
closest match,and will not fail if the value you are looking for does
not exist in your search array; if you use 0, however, the search will
fail if your value is not in the array. I will usually always use zero
(exact match) regardless of how the data is sorted. The only time I
will make use of the 1 or -1 values is when I need to interpolate in a
series of data points and I need to find where the interpolation will
take place in the dataset.
The first argument will be Data6. The second argument will be Ax:Ex,
where x is the row number. You can use the entire row like this
because:- Date values will always be greater than the rain values you
have- Empty cells do not countThe third argument will be zero since you
want an exact match in an array that is not sorted.
The output of the MATCH() function will be the column number of the
maximum (since the first cell of the search range is Ax). The date is 1
to the left: one column less, so MATCH()-1 will give the column where
we can find the date.
To extract the date with INDEX(), you must use the same range as used
to MATCH() the value. The arguments are:- Cell range- Row in the range
(1 if you only select one row of data)- Column in the range
The cell range will be Ax:Ex (exactly what was used in the MATCH()
function), the row will be 1 (only 1 row of data), and the column will
be the result of the MATCH() we did minus 1. This means the formula in
Gx will be:
=INDEX(Ax:Ex;1;MATCH(Hx;Ax:Ex;0)-1)
I hope this helps.Rémy.

Le vendredi 28 mai 2021 à 23:44 +0200, Johnny Rosenberg a écrit :
> Den fre 28 maj 2021 kl 22:49 skrev Hylton Conacher (ZR1HPC) <
> hyl...@conacher.co.za>:
> > Hi,
> > I have LO 7.0.6.2 and am battling with understanding which formula
> > touse as well as the syntax for that formula.
> > I am aware of the availability of vlookup, hlookup, Index/Match
> > formulaand have settled I think on the right one i.e. Index/Match
> > Below is a portion of my spreadsheet that is divided as below with
> > ablank column between each year. What I want to calculate is the
> > date theMax rain occurred. I am OK with the formula to obtain the
> > MAX but I needhelp in constructing a formula to get the
> > corresponding date.
> > I had though the best would be Index and Match but no matter how I
> > enterit I cannot get the date listed under the Date column of 2020
> > or 2021,never mind actually retrieving the year from the same
> > column as the datethe originated.
> > =INDEX(a1:h14;MATCH(h3;a3:h3;0)) just gives me 01/01 for the first
> > dateunder Highest Monthly
> 
> Strange. I give a completely different result, so obviously my
> spreadsheetisn't identical to yours.In which cell did you put this
> formula and what result did you expect?
> > I have looked aver tutorial and their mothers trying to find out
> > whatranges to insert into the Index(Match()) formula with ZERO
> > success.
> > I am manually entering the dates under each year when the max value
> > isrevealed by my formula. Would love to have it automated but my
> > entirespreadsheet covers over 400 rows and more than 52 columns
> > resulting in26 tabs of graphs from the Data sheet.
> > Is my data in the wrong order i.e. should the rainfall value column
> > bebefore the Date it occurred?
> > I do not understand what ranges need to consist of when using
> > Index/Match.
> > Can someone point me to a decent tutorial explaining the different
> > termsi.e. Reference, Row, Column, Range, Search Criterion, Lookup
> > array.
> > 20202021Highest
> > MonthlyDate2020 Rain   Date2021
> > Rain   DateRain01/19   9,5 01/15   3   
> > 2020/01/19  9,502/16   1,5 02/14   3,5 
> > 2021/02/14  3,503/25   3,5 03/14   19  
> > 2021/03/14  1904/11   20  04/26   7
> >2020/04/11  2005/28   27,505/20   43
> >   2021/05/20  4306/11   26  #N/A0  
> >  007/09   85,5#N/

Re: [libreoffice-users] Calc Formula confusion an understanding needed Index/Match

[Johnny Rosenberg]

Den lör 29 maj 2021 kl 21:58 skrev Hylton Conacher (ZR1HPC) <
hyl...@conacher.co.za>:

> Hi Johnny,
>
> On 2021/05/28 23:44, Johnny Rosenberg wrote:
> > Den fre 28 maj 2021 kl 22:49 skrev Hylton Conacher (ZR1HPC) <
> > hyl...@conacher.co.za>:
> >
> >> Hi,
> >>
> >> I have LO 7.0.6.2 and am battling with understanding which formula to
> >> use as well as the syntax for that formula.
> >>
> >> I am aware of the availability of vlookup, hlookup, Index/Match formula
> >> and have settled I think on the right one i.e. Index/Match
> >>
> >> Below is a portion of my spreadsheet that is divided as below with a
> >> blank column between each year. What I want to calculate is the date the
> >> Max rain occurred. I am OK with the formula to obtain the MAX but I need
> >> help in constructing a formula to get the corresponding date.
> >>
> >> I had though the best would be Index and Match but no matter how I enter
> >> it I cannot get the date listed under the Date column of 2020 or 2021,
> >> never mind actually retrieving the year from the same column as the date
> >> the originated.
> >>
> >> =INDEX(a1:h14;MATCH(h3;a3:h3;0)) just gives me 01/01 for the first date
> >> under Highest Monthly
> >>
> >
> > Strange. I give a completely different result, so obviously my
> spreadsheet
> > isn't identical to yours.
> > In which cell did you put this formula and what result did you expect?
> >
> >>
> >> I have looked aver tutorial and their mothers trying to find out what
> >> ranges to insert into the Index(Match()) formula with ZERO success.
> >>
> >> I am manually entering the dates under each year when the max value is
> >> revealed by my formula. Would love to have it automated but my entire
> >> spreadsheet covers over 400 rows and more than 52 columns resulting in
> >> 26 tabs of graphs from the Data sheet.
> >>
> >> Is my data in the wrong order i.e. should the rainfall value column be
> >> before the Date it occurred?
> >>
> >> I do not understand what ranges need to consist of when using
> Index/Match.
> >>
> >> Can someone point me to a decent tutorial explaining the different terms
> >> i.e. Reference, Row, Column, Range, Search Criterion, Lookup array.
> >>
> >> 20202021Highest Monthly
> >> Date2020 Rain   Date2021 Rain   DateRain
> >> 01/19   9,5 01/15   3   2020/01/19  9,5
> >> 02/16   1,5 02/14   3,5 2021/02/14  3,5
> >> 03/25   3,5 03/14   19  2021/03/14  19
> >> 04/11   20  04/26   7   2020/04/11  20
> >> 05/28   27,505/20   43  2021/05/20  43
> >> 06/11   26  #N/A0   0
> >> 07/09   85,5#N/A0   0
> >> 08/28   35  #N/A0   0
> >> 09/02   21  #N/A0   0
> >> 10/28   15  #N/A0   0
> >> 11/06   25  #N/A0   0
> >> 12/26   2   #N/A0   0
> >>
> >> If you want the entire spreadsheet it is available on direct request,
> >> but ultimately I would like to understand how it works.
> >>
> >> This will at least enable to use the formula successfully on newer
> >> versions on LO.
> >>
> >> Regards
> >> Hylton
> >>
> >>
> > I'm not sure what you are trying to do here, so I'll just guess. Just
> > ignore me if I'm totally wrong.
> > So first, I tried to create a spreadsheet following the text above, so
> this
> > is what my spreadsheet looks like:
> > Row 1 and two are just headers.
> > Column A is dates for 2020 formatted as Month/Day.
> > Column B is amount of rain in some unit, not sure which one, so I assume
> > mm, since that's what we use where I live, and it doesn't matter for this
> > question anyway.
> > Column C is empty.
> > Column D is dates for 2021 formatted as Month/Day.
> > Column E is the amount of rain for 2021.
> > Column F is empty.
> > Column G is the column that contains the dates for each month for the
> > respective year with the highest amount of rain, and it's also the column
> > that you wish to automate, is that right?
> > Column H is the highest value of rain in columns B and E for each row.
> >
> > Right so far?
> >
> > If so, you want column G to display the date for each maximum value in
> the
> > B and E column per row, right? Column H  is already figured out, so for
> > instance, H3 contains the following:
> > =MAX(B3;E3)
> >
> > Then you want to automatically display the datum of which this occured,
> or
> > just the year? Well, date or year is only a question about formatting, so
> > let's just leave it to be formatted later. I guess you know how to do
> cell
> > formats and styles anyway.
> > maybe I totally misunderstood the question, but if not, you don't need
> > neither INDEX no

Re: [libreoffice-users] Calc Formula confusion an understanding needed Index/Match

[Hylton Conacher (ZR1HPC)]

Hi Johnny,

On 2021/05/28 23:44, Johnny Rosenberg wrote:

Den fre 28 maj 2021 kl 22:49 skrev Hylton Conacher (ZR1HPC) <
hyl...@conacher.co.za>:


Hi,

I have LO 7.0.6.2 and am battling with understanding which formula to
use as well as the syntax for that formula.

I am aware of the availability of vlookup, hlookup, Index/Match formula
and have settled I think on the right one i.e. Index/Match

Below is a portion of my spreadsheet that is divided as below with a
blank column between each year. What I want to calculate is the date the
Max rain occurred. I am OK with the formula to obtain the MAX but I need
help in constructing a formula to get the corresponding date.

I had though the best would be Index and Match but no matter how I enter
it I cannot get the date listed under the Date column of 2020 or 2021,
never mind actually retrieving the year from the same column as the date
the originated.

=INDEX(a1:h14;MATCH(h3;a3:h3;0)) just gives me 01/01 for the first date
under Highest Monthly



Strange. I give a completely different result, so obviously my spreadsheet
isn't identical to yours.
In which cell did you put this formula and what result did you expect?



I have looked aver tutorial and their mothers trying to find out what
ranges to insert into the Index(Match()) formula with ZERO success.

I am manually entering the dates under each year when the max value is
revealed by my formula. Would love to have it automated but my entire
spreadsheet covers over 400 rows and more than 52 columns resulting in
26 tabs of graphs from the Data sheet.

Is my data in the wrong order i.e. should the rainfall value column be
before the Date it occurred?

I do not understand what ranges need to consist of when using Index/Match.

Can someone point me to a decent tutorial explaining the different terms
i.e. Reference, Row, Column, Range, Search Criterion, Lookup array.

20202021Highest Monthly
Date2020 Rain   Date2021 Rain   DateRain
01/19   9,5 01/15   3   2020/01/19  9,5
02/16   1,5 02/14   3,5 2021/02/14  3,5
03/25   3,5 03/14   19  2021/03/14  19
04/11   20  04/26   7   2020/04/11  20
05/28   27,505/20   43  2021/05/20  43
06/11   26  #N/A0   0
07/09   85,5#N/A0   0
08/28   35  #N/A0   0
09/02   21  #N/A0   0
10/28   15  #N/A0   0
11/06   25  #N/A0   0
12/26   2   #N/A0   0

If you want the entire spreadsheet it is available on direct request,
but ultimately I would like to understand how it works.

This will at least enable to use the formula successfully on newer
versions on LO.

Regards
Hylton



I'm not sure what you are trying to do here, so I'll just guess. Just
ignore me if I'm totally wrong.
So first, I tried to create a spreadsheet following the text above, so this
is what my spreadsheet looks like:
Row 1 and two are just headers.
Column A is dates for 2020 formatted as Month/Day.
Column B is amount of rain in some unit, not sure which one, so I assume
mm, since that's what we use where I live, and it doesn't matter for this
question anyway.
Column C is empty.
Column D is dates for 2021 formatted as Month/Day.
Column E is the amount of rain for 2021.
Column F is empty.
Column G is the column that contains the dates for each month for the
respective year with the highest amount of rain, and it's also the column
that you wish to automate, is that right?
Column H is the highest value of rain in columns B and E for each row.

Right so far?

If so, you want column G to display the date for each maximum value in the
B and E column per row, right? Column H  is already figured out, so for
instance, H3 contains the following:
=MAX(B3;E3)

Then you want to automatically display the datum of which this occured, or
just the year? Well, date or year is only a question about formatting, so
let's just leave it to be formatted later. I guess you know how to do cell
formats and styles anyway.
maybe I totally misunderstood the question, but if not, you don't need
neither INDEX nor MATCH for this. Here's my cell formula in G3, for
instance:
=IF(B3>E3;A3;D3)
So it there was more rain in 2020 than in 2021, display the date in column
A, otherwise display the date in column D.
If you want to fill further down to future dates and only show the values
when the rest of the row is completely entered, you could add another test,
like this:
=IF(OR(A3="";B3="";D3="";E3="");"";IF(B3>E3;A3;D3))
Then you could just fill down and only the relevant cells will display
something and the rest will be blank.
You can do the same thing t

Re: [libreoffice-users] Calc Formula confusion an understanding needed Index/Match

[Johnny Rosenberg]

Den fre 28 maj 2021 kl 22:49 skrev Hylton Conacher (ZR1HPC) <
hyl...@conacher.co.za>:

> Hi,
>
> I have LO 7.0.6.2 and am battling with understanding which formula to
> use as well as the syntax for that formula.
>
> I am aware of the availability of vlookup, hlookup, Index/Match formula
> and have settled I think on the right one i.e. Index/Match
>
> Below is a portion of my spreadsheet that is divided as below with a
> blank column between each year. What I want to calculate is the date the
> Max rain occurred. I am OK with the formula to obtain the MAX but I need
> help in constructing a formula to get the corresponding date.
>
> I had though the best would be Index and Match but no matter how I enter
> it I cannot get the date listed under the Date column of 2020 or 2021,
> never mind actually retrieving the year from the same column as the date
> the originated.
>
> =INDEX(a1:h14;MATCH(h3;a3:h3;0)) just gives me 01/01 for the first date
> under Highest Monthly
>

Strange. I give a completely different result, so obviously my spreadsheet
isn't identical to yours.
In which cell did you put this formula and what result did you expect?

>
> I have looked aver tutorial and their mothers trying to find out what
> ranges to insert into the Index(Match()) formula with ZERO success.
>
> I am manually entering the dates under each year when the max value is
> revealed by my formula. Would love to have it automated but my entire
> spreadsheet covers over 400 rows and more than 52 columns resulting in
> 26 tabs of graphs from the Data sheet.
>
> Is my data in the wrong order i.e. should the rainfall value column be
> before the Date it occurred?
>
> I do not understand what ranges need to consist of when using Index/Match.
>
> Can someone point me to a decent tutorial explaining the different terms
> i.e. Reference, Row, Column, Range, Search Criterion, Lookup array.
>
> 20202021Highest Monthly
> Date2020 Rain   Date2021 Rain   DateRain
> 01/19   9,5 01/15   3   2020/01/19  9,5
> 02/16   1,5 02/14   3,5 2021/02/14  3,5
> 03/25   3,5 03/14   19  2021/03/14  19
> 04/11   20  04/26   7   2020/04/11  20
> 05/28   27,505/20   43  2021/05/20  43
> 06/11   26  #N/A0   0
> 07/09   85,5#N/A0   0
> 08/28   35  #N/A0   0
> 09/02   21  #N/A0   0
> 10/28   15  #N/A0   0
> 11/06   25  #N/A0   0
> 12/26   2   #N/A0   0
>
> If you want the entire spreadsheet it is available on direct request,
> but ultimately I would like to understand how it works.
>
> This will at least enable to use the formula successfully on newer
> versions on LO.
>
> Regards
> Hylton
>
>
I'm not sure what you are trying to do here, so I'll just guess. Just
ignore me if I'm totally wrong.
So first, I tried to create a spreadsheet following the text above, so this
is what my spreadsheet looks like:
Row 1 and two are just headers.
Column A is dates for 2020 formatted as Month/Day.
Column B is amount of rain in some unit, not sure which one, so I assume
mm, since that's what we use where I live, and it doesn't matter for this
question anyway.
Column C is empty.
Column D is dates for 2021 formatted as Month/Day.
Column E is the amount of rain for 2021.
Column F is empty.
Column G is the column that contains the dates for each month for the
respective year with the highest amount of rain, and it's also the column
that you wish to automate, is that right?
Column H is the highest value of rain in columns B and E for each row.

Right so far?

If so, you want column G to display the date for each maximum value in the
B and E column per row, right? Column H  is already figured out, so for
instance, H3 contains the following:
=MAX(B3;E3)

Then you want to automatically display the datum of which this occured, or
just the year? Well, date or year is only a question about formatting, so
let's just leave it to be formatted later. I guess you know how to do cell
formats and styles anyway.
maybe I totally misunderstood the question, but if not, you don't need
neither INDEX nor MATCH for this. Here's my cell formula in G3, for
instance:
=IF(B3>E3;A3;D3)
So it there was more rain in 2020 than in 2021, display the date in column
A, otherwise display the date in column D.
If you want to fill further down to future dates and only show the values
when the rest of the row is completely entered, you could add another test,
like this:
=IF(OR(A3="";B3="";D3="";E3="");"";IF(B3>E3;A3;D3))
Then you could just fill down and only the relevant cells will display
something and the r

[libreoffice-users] Calc Formula confusion an understanding needed Index/Match

[Hylton Conacher (ZR1HPC)]

Hi,

I have LO 7.0.6.2 and am battling with understanding which formula to 
use as well as the syntax for that formula.


I am aware of the availability of vlookup, hlookup, Index/Match formula 
and have settled I think on the right one i.e. Index/Match


Below is a portion of my spreadsheet that is divided as below with a 
blank column between each year. What I want to calculate is the date the 
Max rain occurred. I am OK with the formula to obtain the MAX but I need 
help in constructing a formula to get the corresponding date.


I had though the best would be Index and Match but no matter how I enter 
it I cannot get the date listed under the Date column of 2020 or 2021, 
never mind actually retrieving the year from the same column as the date 
the originated.


=INDEX(a1:h14;MATCH(h3;a3:h3;0)) just gives me 01/01 for the first date 
under Highest Monthly


I have looked aver tutorial and their mothers trying to find out what 
ranges to insert into the Index(Match()) formula with ZERO success.


I am manually entering the dates under each year when the max value is 
revealed by my formula. Would love to have it automated but my entire 
spreadsheet covers over 400 rows and more than 52 columns resulting in 
26 tabs of graphs from the Data sheet.


Is my data in the wrong order i.e. should the rainfall value column be 
before the Date it occurred?


I do not understand what ranges need to consist of when using Index/Match.

Can someone point me to a decent tutorial explaining the different terms 
i.e. Reference, Row, Column, Range, Search Criterion, Lookup array.


20202021Highest Monthly 
Date2020 Rain   Date2021 Rain   DateRain
01/19   9,5 01/15   3   2020/01/19  9,5
02/16   1,5 02/14   3,5 2021/02/14  3,5
03/25   3,5 03/14   19  2021/03/14  19
04/11   20  04/26   7   2020/04/11  20
05/28   27,505/20   43  2021/05/20  43
06/11   26  #N/A0   0
07/09   85,5#N/A0   0
08/28   35  #N/A0   0
09/02   21  #N/A0   0
10/28   15  #N/A0   0
11/06   25  #N/A0   0
12/26   2   #N/A0   0

If you want the entire spreadsheet it is available on direct request, 
but ultimately I would like to understand how it works.


This will at least enable to use the formula successfully on newer 
versions on LO.


Regards
Hylton

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc: Formatting cell with decimal point but no decimal digits

[Michael D. Setzer II]

On 11 May 2021 at 13:24, Philip Jackson wrote:

Subject:Re: [libreoffice-users] Calc: Formatting cell 
with decimal point but
no decimal digits
To: users@global.libreoffice.org
From:   Philip Jackson 
Date sent:  Tue, 11 May 2021 13:24:26 +0200

> On 11/05/2021 03:22, Joel Roth wrote:
> >> Using LibreOffice 7.0.5.2 on macOS High Sierra (10.13.6), setting the
> >> number format to "#,##0\." (without the quotes, of course) works for me.
> >> ("#,##0.." also works, but that's too bizarre.;-)
> >
> > On my version (6.1.5) both of these formats display the cell
> > contents with a trailing decimal point, but with this
> > formatting, inputting 123.45 results in cell content 12345.
> 
> This thread caught my eye and I can't imagine the use case for displaying the 
> digital point as the OP wishes - but I gave his model a try as I did also 
> with the two expressions proposed by Chuck. I am using Calc 6.4.7.2 in Ubuntu 
> 20.04.2  The results were surprising.
> 
> I entered 123.59 in some cells in 3 columns formatted as: -
> 
> 1) Numbers #,##0"."
> 
> 2) Numbers #,##0\.
> 
> 3) Numbers  #,##0..
> 
> All three columns then displayed my entries as "124."   ie, rounded up and 
> with a final "."
> 
> But, when I entered 123.59 into other empty cells in those 3 pre-formatted 
> columns, my new entries were displayed as "12,359." in each case.
> 
> So applying the format to an already entered number gives a final "." - I 
> don't know whether the rounding up is acceptable or not. But I imagine the OP 
> wants to enter his number into cells that already have his format applied.
> 
> Curiously, when I selected my 3 columns and attempted to return to 
> 'normality' by formatting the cells to Numbers > General, nothing happened. I 
> had to change to some other format than Number (eg date) and then back to 
> numbers-general to get a result.
> 
> When Numbers-General was eventually restored, the "124."  entries were 
> returned to their original states of "123.59" but those entries applied to 
> the pre-formatted columns and which then displayed as "12,359." became 
> "12359" under the restored numbers-general regime.
> 
> Philip
> 

When I saw the original message my though was that the 
person made a typo and had actually used #,##0"," 
instead of #,##0"."

But waited for someone to come up with another 
reason?? Would just be one key off.


> 
> 
> -- 
> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> Problems? 
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy
> 


++
 Michael D. Setzer II - Computer Science Instructor 
(Retired) 
 mailto:mi...@guam.net
 mailto:msetze...@gmail.com
 Guam - Where America's Day Begins
 G4L Disk Imaging Project maintainer 
 http://sourceforge.net/projects/g4l/
++




-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc: Formatting cell with decimal point but no decimal digits

[Philip Jackson]

On 11/05/2021 03:22, Joel Roth wrote:

Using LibreOffice 7.0.5.2 on macOS High Sierra (10.13.6), setting the
number format to "#,##0\." (without the quotes, of course) works for me.
("#,##0.." also works, but that's too bizarre.;-)


On my version (6.1.5) both of these formats display the cell
contents with a trailing decimal point, but with this
formatting, inputting 123.45 results in cell content 12345.


This thread caught my eye and I can't imagine the use case for displaying the 
digital point as the OP wishes - but I gave his model a try as I did also with 
the two expressions proposed by Chuck. I am using Calc 6.4.7.2 in Ubuntu 
20.04.2  The results were surprising.

I entered 123.59 in some cells in 3 columns formatted as: -

1) Numbers #,##0"."

2) Numbers #,##0\.

3) Numbers  #,##0..

All three columns then displayed my entries as "124."   ie, rounded up and with a final 
"."

But, when I entered 123.59 into other empty cells in those 3 pre-formatted columns, my 
new entries were displayed as "12,359." in each case.

So applying the format to an already entered number gives a final "." - I don't 
know whether the rounding up is acceptable or not. But I imagine the OP wants to enter 
his number into cells that already have his format applied.

Curiously, when I selected my 3 columns and attempted to return to 'normality' by 
formatting the cells to Numbers > General, nothing happened. I had to change to 
some other format than Number (eg date) and then back to numbers-general to get a 
result.

When Numbers-General was eventually restored, the "124."  entries were returned to their original states of 
"123.59" but those entries applied to the pre-formatted columns and which then displayed as 
"12,359." became "12359" under the restored numbers-general regime.

Philip



--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc: Formatting cell with decimal point but no decimal digits

[Joel Roth]

On Mon, May 10, 2021 at 05:18:46PM -0700, Chuck Spalding wrote:
> Using LibreOffice 7.0.5.2 on macOS High Sierra (10.13.6), setting the
> number format to "#,##0\." (without the quotes, of course) works for me.
> ("#,##0.." also works, but that's too bizarre. ;-)
 
On my version (6.1.5) both of these formats display the cell
contents with a trailing decimal point, but with this
formatting, inputting 123.45 results in cell content 12345.


> Chuck
> 
> On Mon, May 10, 2021 at 12:37 PM Joel Roth  wrote:
> 
> > On Mon, May 10, 2021 at 12:05:04AM -0600, Joe Conner wrote:
> > > In calc, you could format the cell as currency.
> >
> > Thanks, I tried this, but wasn't able to display a terminal
> > decimal point.
> >
> > > On 5/9/21 3:36 PM, Joel Roth wrote:
> > > > Hi list,
> > > >
> > > > I'd like to input 123.45 and format it with a decimal point
> > > > but no decimal digits, in this example "123." . Previously I
> > > > used the formatting code #,##0"." but now it causes an input
> > > > of 123.45 to be received as 12,345, losing the decimal.
> > > >
> > > > Any ideas?
> > > >
> > > > Thanks in advance.
> >
> [snip]

-- 
Joel Roth

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc: Formatting cell with decimal point but no decimal digits

[Chuck Spalding]

Using LibreOffice 7.0.5.2 on macOS High Sierra (10.13.6), setting the
number format to "#,##0\." (without the quotes, of course) works for me.
("#,##0.." also works, but that's too bizarre. ;-)

Chuck

On Mon, May 10, 2021 at 12:37 PM Joel Roth  wrote:

> On Mon, May 10, 2021 at 12:05:04AM -0600, Joe Conner wrote:
> > In calc, you could format the cell as currency.
>
> Thanks, I tried this, but wasn't able to display a terminal
> decimal point.
>
> > On 5/9/21 3:36 PM, Joel Roth wrote:
> > > Hi list,
> > >
> > > I'd like to input 123.45 and format it with a decimal point
> > > but no decimal digits, in this example "123." . Previously I
> > > used the formatting code #,##0"." but now it causes an input
> > > of 123.45 to be received as 12,345, losing the decimal.
> > >
> > > Any ideas?
> > >
> > > Thanks in advance.
>
[snip]

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc: Formatting cell with decimal point but no decimal digits

[Joel Roth]

On Mon, May 10, 2021 at 12:05:04AM -0600, Joe Conner wrote:
> In calc, you could format the cell as currency.

Thanks, I tried this, but wasn't able to display a terminal
decimal point. 

> On 5/9/21 3:36 PM, Joel Roth wrote:
> > Hi list,
> > 
> > I'd like to input 123.45 and format it with a decimal point
> > but no decimal digits, in this example "123." . Previously I
> > used the formatting code #,##0"." but now it causes an input
> > of 123.45 to be received as 12,345, losing the decimal.
> > 
> > Any ideas?
> > 
> > Thanks in advance.
> > 
> > 
> 
> -- 
> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> Problems? 
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy

-- 
Joel Roth

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] Calc: Formatting cell with decimal point but no decimal digits

[Mike Flannigan]

Type
locale
in a terminal.  What locale are you?
I am 'en_US'.

This works for me:
Type 123.45 in a cell
Highlight the cell
Format - Cells - 'Numbers' tab -
Make 'Decimal places:' under Options zero (0).


Mike



On 5/10/21 1:04 AM, users+h...@global.libreoffice.org wrote:

Hi list,

I'd like to input 123.45 and format it with a decimal point
but no decimal digits, in this example "123." . Previously I
used the formatting code #,##0"." but now it causes an input
of 123.45 to be received as 12,345, losing the decimal.

Any ideas?

Thanks in advance.



--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc: Formatting cell with decimal point but no decimal digits

[Joe Conner]

In calc, you could format the cell as currency.

On 5/9/21 3:36 PM, Joel Roth wrote:

Hi list,

I'd like to input 123.45 and format it with a decimal point
but no decimal digits, in this example "123." . Previously I
used the formatting code #,##0"." but now it causes an input
of 123.45 to be received as 12,345, losing the decimal.

Any ideas?

Thanks in advance.




--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] Calc: Formatting cell with decimal point but no decimal digits

[Joel Roth]

Hi list,

I'd like to input 123.45 and format it with a decimal point
but no decimal digits, in this example "123." . Previously I
used the formatting code #,##0"." but now it causes an input
of 123.45 to be received as 12,345, losing the decimal. 

Any ideas? 

Thanks in advance.


-- 
Joel Roth

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [BULK] Re: [libreoffice-users] calc: index column A with column B

[Rob Jasper]

James,
There are 2 basic ways to accomplish this:

Round the values at calculation time, so they’ll become real 0’s

Change the formula to MINIFS(B:B;B:B;">=1”)

Success,
Rob

> Op 29 jan. 2021, om 20:06 heeft James  het volgende 
> geschreven:
> 
> Another problem.
> 
> Some of the numbers display as 0 but are calculated so have fractions like
> 0.000812344372353
> I want to find the last row that displays as non-zero.
>   
> 
> On 2021-01-29 6:30 a.m., Rob Jasper wrote
>> James,
>> 
>> Since a MINIF function does not exist you can use MINIFS.
>> 
>> Change MIN(B:B) to MINIFS(B:B;B:B;">0”)
>> 
>> Success,
>> Rob
>> 
>> 
>>> Op 29 jan. 2021, om 03:59 heeft James  
>>>  het volgende geschreven:
>>> 
I want to find the row in column A that corresponds to the row of a
 
 value in column B.
 
eg.
 
rowAB
1  10   22
2  20   15
3  30   3000
4  40   10
 
=some_function('A',minimum_function(B));
 
where minimum_function(B) returns 4 and some_function('A',4) returns the
 
 value in column A at row 4.
>>> I asked the previous and someone gave me the formula:
>>> 
>>> =INDIRECT("$A"&MATCH(MIN(B:B);B:B;0))
>>> 
>>> 
>>> It worked fine but now I have some rows with zeros (more than one row):
>>> row A   B
>>> 1   10  22
>>> 2   20  15
>>> 3   30  3000
>>> 4   40  10
>>> 5   50  0
>>> 6   60  0
>>> 
>>> Is there a MINBUTGREATERTHANZERO function?


-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] calc: index column A with column B

[Johnny Rosenberg]

Den fre 29 jan. 2021 kl 20:16 skrev James :

> Another problem.
>
> Some of the numbers display as 0 but are calculated so have fractions like
>
> 0.000812344372353
> I want to find the last row that displays as non-zero.
>

One way to solve it is to round all involved cell values, if all the extra
decimals are not necessary. Depending on what your formulas look like, you
can probably wrap an ROUND() around each one of them. For instance
=ROUND(MyBigAndComplicatedFormula;5)

There's also a (global) setting to make all cell values exactly as they are
viewed, but that would affect all your spreadsheets, I guess. In many cases
you probably don't want that.


Kind regards

Johnny Rosenberg


>
>
> On 2021-01-29 6:30 a.m., Rob Jasper wrote
>
> > James,
> >
> > Since a MINIF function does not exist you can use MINIFS.
> >
> > Change MIN(B:B) to MINIFS(B:B;B:B;">0”)
> >
> > Success,
> > Rob
> >
> >
> >> Op 29 jan. 2021, om 03:59 heeft James  het
> volgende geschreven:
> >>
> >>> I want to find the row in column A that corresponds to the row of a
> >>>
> >>> value in column B.
> >>>
> >>> eg.
> >>>
> >>> rowAB
> >>> 1  10   22
> >>> 2  20   15
> >>> 3  30   3000
> >>> 4  40   10
> >>>
> >>> =some_function('A',minimum_function(B));
> >>>
> >>> where minimum_function(B) returns 4 and some_function('A',4)
> returns the
> >>>
> >>> value in column A at row 4.
> >> I asked the previous and someone gave me the formula:
> >>
> >> =INDIRECT("$A"&MATCH(MIN(B:B);B:B;0))
> >>
> >>
> >> It worked fine but now I have some rows with zeros (more than one row):
> >> row  A   B
> >> 1   10  22
> >> 2   20  15
> >> 3   30  3000
> >> 4   40  10
> >> 550  0
> >> 660  0
> >>
> >> Is there a MINBUTGREATERTHANZERO function?
>
>
> --
> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> Problems?
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy
>

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] calc: index column A with column B

[Michael D. Setzer II]

Then use >0.001 or whatever value works instead of >0


On 29 Jan 2021 at 14:06, James wrote:

Subject:    Re: [libreoffice-users] calc: index column A with 
column B
To: Rob Jasper 
Copies to:  users@global.libreoffice.org
From:   James 
Date sent:  Fri, 29 Jan 2021 14:06:08 -0500

> Another problem.
> 
> Some of the numbers display as 0 but are calculated so have fractions like
> 
> 0.000812344372353
> I want to find the last row that displays as non-zero.
>   
> 
> On 2021-01-29 6:30 a.m., Rob Jasper wrote
> 
> > James,
> >
> > Since a MINIF function does not exist you can use MINIFS.
> >
> > Change MIN(B:B) to MINIFS(B:B;B:B;">0")
> >
> > Success,
> > Rob
> >
> >
> >> Op 29 jan. 2021, om 03:59 heeft James  het volgende 
> >> geschreven:
> >>
> >>> I want to find the row in column A that corresponds to the row of a
> >>>
> >>> value in column B.
> >>>
> >>> eg.
> >>>
> >>> rowAB
> >>> 1  10   22
> >>> 2  20   15
> >>> 3  30   3000
> >>> 4  40   10
> >>>
> >>> =some_function('A',minimum_function(B));
> >>>
> >>> where minimum_function(B) returns 4 and some_function('A',4) returns 
> >>> the
> >>>
> >>> value in column A at row 4.
> >> I asked the previous and someone gave me the formula:
> >>
> >> =INDIRECT("$A"&MATCH(MIN(B:B);B:B;0))
> >>
> >>
> >> It worked fine but now I have some rows with zeros (more than one row):
> >> rowA   B
> >> 1   10  22
> >> 2   20  15
> >> 3   30  3000
> >> 4   40  10
> >> 5  50  0
> >> 6  60  0
> >>
> >> Is there a MINBUTGREATERTHANZERO function?
> 
> 
> -- 
> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> Problems? 
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy


++
 Michael D. Setzer II - Computer Science Instructor (Retired) 
 mailto:mi...@guam.net
 mailto:msetze...@gmail.com
 Guam - Where America's Day Begins
 G4L Disk Imaging Project maintainer 
 http://sourceforge.net/projects/g4l/
++




-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] calc: index column A with column B

[James]

Another problem.

Some of the numbers display as 0 but are calculated so have fractions like

0.000812344372353
I want to find the last row that displays as non-zero.  


On 2021-01-29 6:30 a.m., Rob Jasper wrote


James,

Since a MINIF function does not exist you can use MINIFS.

Change MIN(B:B) to MINIFS(B:B;B:B;">0”)

Success,
Rob



Op 29 jan. 2021, om 03:59 heeft James  het volgende 
geschreven:


I want to find the row in column A that corresponds to the row of a

value in column B.

eg.

rowAB
1  10   22
2  20   15
3  30   3000
4  40   10

=some_function('A',minimum_function(B));

where minimum_function(B) returns 4 and some_function('A',4) returns the

value in column A at row 4.

I asked the previous and someone gave me the formula:

=INDIRECT("$A"&MATCH(MIN(B:B);B:B;0))


It worked fine but now I have some rows with zeros (more than one row):
row A   B
1   10  22
2   20  15
3   30  3000
4   40  10
5   50  0
6   60  0

Is there a MINBUTGREATERTHANZERO function?



--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] calc: index column A with column B

[Rob Jasper]

James,

Since a MINIF function does not exist you can use MINIFS.

Change MIN(B:B) to MINIFS(B:B;B:B;">0”)

Success,
Rob


> Op 29 jan. 2021, om 03:59 heeft James  het volgende 
> geschreven:
> 
>>I want to find the row in column A that corresponds to the row of a
>> 
>> value in column B.
>> 
>>eg.
>> 
>>rowAB
>>1  10   22
>>2  20   15
>>3  30   3000
>>4  40   10
>> 
>>=some_function('A',minimum_function(B));
>> 
>>where minimum_function(B) returns 4 and some_function('A',4) returns the
>> 
>> value in column A at row 4.
> I asked the previous and someone gave me the formula:
> 
> =INDIRECT("$A"&MATCH(MIN(B:B);B:B;0))
> 
> 
> It worked fine but now I have some rows with zeros (more than one row):
> row   A   B
> 1   10  22
> 2   20  15
> 3   30  3000
> 4   40  10
> 5 50  0
> 6 60  0
> 
> Is there a MINBUTGREATERTHANZERO function?
> 
> 
> 
> -- 
> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> Problems? 
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy


-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] calc: index column A with column B

[James]

Great, thanks.

On 2021-01-24 5:59 p.m., Rob Jasper wrote:

Is this what you mean:

=INDIRECT("$A"&MATCH(MIN(B:B);B:B;0))


Op 24 jan. 2021, om 23:14 heeft James > het volgende geschreven:


I want to find the row in column A that corresponds to the row of a 
value in column B.


eg.

row    AB

1      1022

2      2015

3      303000

4      4010

=some_function('A',minimum_function(B));

where minimum_function(B) returns 4 and some_function('A',4) returns 
the value in column A at row 4.




--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org 

Problems? 
https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ 

Posting guidelines + more: 
https://wiki.documentfoundation.org/Netiquette 

List archive: https://listarchives.libreoffice.org/global/users/ 

Privacy Policy: https://www.documentfoundation.org/privacy 






--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] calc: index column A with column B

[Chuck Spalding]

I think you meant to write this:

=INDIRECT("$A"&MATCH(MIN(B*1*:B*4*);B*1*:B*4*;0))


Chuck

On Sun, Jan 24, 2021 at 3:22 PM Rob Jasper  wrote:

> Is this what you mean:
>
> =INDIRECT("$A"&MATCH(MIN(B:B);B:B;0))
>
>
> > Op 24 jan. 2021, om 23:14 heeft James  het volgende
> geschreven:
> >
> > I want to find the row in column A that corresponds to the row of a
> value in column B.
> >
> > eg.
> >
> > rowAB
> >
> > 1  10   22
> >
> > 2  20   15
> >
> > 3  30   3000
> >
> > 4  40   10
> >
> > =some_function('A',minimum_function(B));
> >
> > where minimum_function(B) returns 4 and some_function('A',4) returns the
> value in column A at row 4.
> >
> >
> >
> > --
> > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> > Problems?
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> > Posting guidelines + more:
> https://wiki.documentfoundation.org/Netiquette
> > List archive: https://listarchives.libreoffice.org/global/users/
> > Privacy Policy: https://www.documentfoundation.org/privacy
>
>
> --
> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> Problems?
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy
>
>

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] calc: index column A with column B

[Rob Jasper]

Is this what you mean:

=INDIRECT("$A"&MATCH(MIN(B:B);B:B;0))


> Op 24 jan. 2021, om 23:14 heeft James  het volgende 
> geschreven:
> 
> I want to find the row in column A that corresponds to the row of a value in 
> column B.
> 
> eg.
> 
> rowAB
> 
> 1  10   22
> 
> 2  20   15
> 
> 3  30   3000
> 
> 4  40   10
> 
> =some_function('A',minimum_function(B));
> 
> where minimum_function(B) returns 4 and some_function('A',4) returns the 
> value in column A at row 4.
> 
> 
> 
> -- 
> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> Problems? 
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy


-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] calc: index column A with column B

[James]

I want to find the row in column A that corresponds to the row of a value in 
column B.

eg.

row    A  B

1      10     22

2      20     15

3      30     3000

4      40     10

=some_function('A',minimum_function(B));

where minimum_function(B) returns 4 and some_function('A',4) returns the value 
in column A at row 4.



--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] calc chart problems

[Brian Barker]

At 19:18 18/01/2021 -0500, Marianne Keating wrote:
I am using 4.4.7.2, under Windows 7, because it 
seems more than adequate for my purposes, and I 
am somewhat dismayed by the number of 
regressions noted for later versions in the comments here.


It's probably unwise to use any out-of-date 
software, which will not have been patched for 
security flaws and so on. Try a later version, notwithstanding regressions.


Calc chart legends. What could be simpler, yet 
more impossible? I have cracked this at sometime 
in the past, as I have just one spreadsheet with 
my own chart legend text in my choice of fonts, 
but can see no way of regaining that control for 
other charts in my armamentarium. If I 
right-click a chart, I am presented with 15 options.


First, what you see in the context menu depends 
on the current state of selection of the chart. 
What you are describing is the context menu seen 
when the chart is simply selected - so that it 
shows the eight coloured handles.


One is marked “Text”, which seems promising, but 
apparently deals with *positioning*, not content.


That's right: this deals with positioning of items generally.

The thing which it definitely lacks is any 
ability to control the legend text contents, or font.


To do that, you need to select the chart first.


If I double left-click ...


And that's how you select the chart - so that it shows a grey border.

... and then right-click, I now have 9 options, 
including one which will delete a legend, 
(leaving me unsure of what happens if the chart has more than one legend).


No problem: the legend is what labels the various 
data ranges, so charts have only one legend.


But, in any case, no control, beyond excision, 
over the legend text or its font.


To format the legend, first select the legend - 
so that *it* shows the eight coloured handles - 
and then use Format Legend... from the context menu.


When I double left-click, I get an interesting 
collection of mostly-graphic symbols at the bottom of my screen, ...


That's the Drawing toolbar.

One of these is a capital T in a dotted-line 
box, which, when hovered-over, reads: “Text 
Box”. Sounds promising, but what does it do, ...


It creates a (by default invisible) rectangular box to contain text.


... and how does one invoke whatever its action may be?


Click the "T" ("A" in the more recent version you 
should be using!). Note that the cursor (when in 
the chart) changes appearance. Drag the cursor to define the desired box.


None of these symbols can be dragged onto the 
chart, and clicking them – single or double, 
left or right – produces no obvious result of any kind.


First click; then drag in (not into) the chart.

They have interesting titles, and, if they did 
anything, might be quite useful.


You give the distinct impression that you have 
not read any of the documentation. Everything is 
in "Adding drawing objects to charts" in Chapter 
3, "Creating Charts and Graphs" of the Calc Guide.


My first question, then, is: /how do I gain 
control over chart legend text & font?/


See above. (Though I think you may be asking not 
about the chart legend but instead axis titles, or even a chart title.)



And a subsidiary wonderment: /why is it so hard to find this answer?/


It's not. Again it's all in "Legends" and 
"Titles, subtitles, and axis names" in Chapter 3, 
"Creating Charts and Graphs", of the Calc Guide.



From the on-line manual:


Two points here:
o I find it easier not to use on-line facilities 
but instead to download the guides. If you choose 
the PDF versions, you can display them locally 
and easily search for what you require.
o I'm not at all sure why you choose to copy lots 
of material from documentation in your message. 
Surely this is what *you* should be reading, not anyone offering to help you?



To edit a chart legend:
1. Double-click on the chart. A gray border 
appears around the chart and the menu bar now 
contains commands for editing the objects in the chart.
2. Choose Format - Legend or double-click on the 
legend. This opens the Legend dialog.

3. Choose from the available tabs to make modifications, then click OK.
This is no help, as the “available tabs” do 
*not* allow editing the text or font.


You can change the font on the Font tab. You can 
change the text by changing the contents of the 
spreadsheet cells that you originally included 
for this purpose. Alternatively you can use Data 
Ranges... from the context menu to allow you to 
change which cells are referenced.



From the on-line edition of the 4.1 Calc Guide we have, at ppg 88-89:
To add a legend to your chart:
[...]
To remove a legend from your chart:
[...]
Again, the user can add or remove, but not edit.


Well, just as advertised, then?!


Another dead-end source illuminates another Calc problem:
https://ask.libreoffice.org/en/question/...


There is no guarantee that answers on any forum - 
or indeed here on the Users mailing list - will be valuable or correc

Re: [libreoffice-users] calc chart problems

[Remy Gauthier]

Greetings Marianne,Things have changed a lot since the version you are
using, and it may be difficult to provide the information you are
looking for. I did a bit of digging, and I managed to find this
document:
https://wiki.documentfoundation.org/images/b/bb/CG3403-ChartsAndGraphs.pdf
which comes from this location:
https://wiki.documentfoundation.org/Documentation/Publications/3.x
It is for CALC 3.4, but in looking at the release notes (
https://wiki.documentfoundation.org/ReleaseNotes) between that version
and yours, there does not seem to be any changes to the way to manage
graphs in 3.4 and your version (except for a new chart type), so you
should be able to find the information you are looking for.
I hope this helps,Rémy.
Le lundi 18 janvier 2021 à 19:18 -0500, marianne-x a écrit :
> Ahoy all:
> My apologies if this turns out to be a redundancy, but I originally
> submitted it some 4 hours ago and it hasn't shown up yet.
> Some 20 years ago, I frequently received questions such as those I
> pose below, and was able to easily answer them for others.
> By now, I seem to have aged out, and am flummoxed by difficulties
> that should be rudimentary.
> So, should any reader deign to attack my elementary problems, please
> keep it simple (& simpleminded) so that I have a chance of
> understanding.
> I am using 4.4.7.2, under Windows (or, as we call it, /Curtains/) 7,
> because it seems more than adequate for my purposes, and I am
> somewhat dismayed by the number of regressions noted for later
> versions in the comments here.
> Calc chart legends. What could be simpler, yet more impossible? I
> have cracked this at sometime in the past, as I have just one
> spreadsheet with my own chart legend text in my choice of fonts, but
> can see no way of regaining that control for other charts in my
> armamentarium.
> If I right-click a chart, I am presented with 15 options. One is
> marked “Text”, which seems promising, but apparently deals with
> *positioning*, not content. The thing which it definitely lacks is
> any ability to control the legend text contents, or font.
> If I double left-click and then right-click, I now have 9 options,
> including one which will delete a legend, (leaving me unsure of what
> happens if the chart has more than one legend). But, in any case, no
> control, beyond excision, over the legend text or its font.
> When I double left-click, I get an interesting collection of mostly-
> graphic symbols at the bottom of my screen, where the “Find” box
> normally lives. One of these is a capital T in a dotted-line box,
> which, when hovered-over, reads: “Text Box”. Sounds promising, but
> what does it do, and how does one invoke whatever its action may be?
> None of these symbols can be dragged onto the chart, and clicking
> them – single or double, left or right – produces no obvious result
> of any kind. They have interesting titles, and, if they did anything,
> might be quite useful.
> My first question, then, is: /how do I gain control over chart legend
> text & font?/ And a subsidiary wonderment: /why is it so hard to find
> this answer?/
> Further routes to failure on this question:
>  From the on-line manual:
> https://help.libreoffice.org/6.1/en-US/text/shared/guide/chart_legend.html?DbPAR=SHARED
>  US/text/shared/guide/chart_legend.html?DbPAR=SHARED>
> we have, (appreciating that this is targeted at 6.1; probably an
> irrelevant distinction):
> 
>   Editing Chart Legends
> To edit a chart legend:
>  1.
> Double-click on the chart.
> A gray border appears around the chart and the menu bar now
> containscommands for editing the objects in the chart.
>  2.
> Choose Format - Legend or double-click on the legend. This opens
> theLegend dialog.
>  3.
> Choose from the available tabs to make modifications, then click
> OK.
> This is no help, as the “available tabs” do *not* allow editing the
> text or font.
>  From the on-line edition of the 4.1 Calc Guide we have, at ppg 88-
> 89:
> *Adding or removing chart elements*
> /*Legends*/
> To add a legend to your chart:
>  1.
> Select the chart by double-clicking on it to enter edit mode.
> Thechart should now be surrounded by a gray border.
>  2.
> Go to*Insert>Legend*onthe main menu bar to open the
> Legenddialog. This dialog is similar to the *Display
> legend*section on theChart Wizard dialogshown in Error: Reference
> source not foundon pageError: Reference source not found.
>  3.
> Select the *Display legend* checkbox and where you want the
> legenddisplayed on your chart – *Left*, *Right*, *Top* or
> *Bottom*.
>  4.
> Click *OK*to close the dialog.
>  5.
> Alternatively,right-click inthe chart area and
> select*Insert**Legend*from the context menu to insert a legend in
> the defaultposition on the right side of the chart.
>  6.
> Click outside the chart to leave edit mode.
> To remove a legend from your chart:
>  1.
>  

[libreoffice-users] calc chart problems

[marianne-x]

Ahoy all:

My apologies if this turns out to be a redundancy, but I originally 
submitted it some 4 hours ago and it hasn't shown up yet.


Some 20 years ago, I frequently received questions such as those I pose 
below, and was able to easily answer them for others.


By now, I seem to have aged out, and am flummoxed by difficulties that 
should be rudimentary.


So, should any reader deign to attack my elementary problems, please 
keep it simple (& simpleminded) so that I have a chance of understanding.


I am using 4.4.7.2, under Windows (or, as we call it, /Curtains/) 7, 
because it seems more than adequate for my purposes, and I am somewhat 
dismayed by the number of regressions noted for later versions in the 
comments here.


Calc chart legends. What could be simpler, yet more impossible? I have 
cracked this at sometime in the past, as I have just one spreadsheet 
with my own chart legend text in my choice of fonts, but can see no way 
of regaining that control for other charts in my armamentarium.


If I right-click a chart, I am presented with 15 options. One is marked 
“Text”, which seems promising, but apparently deals with *positioning*, 
not content. The thing which it definitely lacks is any ability to 
control the legend text contents, or font.


If I double left-click and then right-click, I now have 9 options, 
including one which will delete a legend, (leaving me unsure of what 
happens if the chart has more than one legend). But, in any case, no 
control, beyond excision, over the legend text or its font.


When I double left-click, I get an interesting collection of 
mostly-graphic symbols at the bottom of my screen, where the “Find” box 
normally lives. One of these is a capital T in a dotted-line box, which, 
when hovered-over, reads: “Text Box”. Sounds promising, but what does it 
do, and how does one invoke whatever its action may be?


None of these symbols can be dragged onto the chart, and clicking them – 
single or double, left or right – produces no obvious result of any 
kind. They have interesting titles, and, if they did anything, might be 
quite useful.


My first question, then, is: /how do I gain control over chart legend 
text & font?/ And a subsidiary wonderment: /why is it so hard to find 
this answer?/


Further routes to failure on this question:

From the on-line manual:

https://help.libreoffice.org/6.1/en-US/text/shared/guide/chart_legend.html?DbPAR=SHARED 



we have, (appreciating that this is targeted at 6.1; probably an 
irrelevant distinction):



 Editing Chart Legends

To edit a chart legend:

1.

   Double-click on the chart.

   A gray border appears around the chart and the menu bar now contains
   commands for editing the objects in the chart.

2.

   Choose Format - Legend or double-click on the legend. This opens the
   Legend dialog.

3.

   Choose from the available tabs to make modifications, then click OK.

This is no help, as the “available tabs” do *not* allow editing the text 
or font.


From the on-line edition of the 4.1 Calc Guide we have, at ppg 88-89:

*Adding or removing chart elements*

/*Legends*/

To add a legend to your chart:

1.

   Select the chart by double-clicking on it to enter edit mode. The
   chart should now be surrounded by a gray border.

2.

   Go to*Insert>Legend*onthe main menu bar to open the Legend
   dialog. This dialog is similar to the *Display legend*section on the
   Chart Wizard dialogshown in Error: Reference source not foundon page
   Error: Reference source not found.

3.

   Select the *Display legend* checkbox and where you want the legend
   displayed on your chart – *Left*, *Right*, *Top* or *Bottom*.

4.

   Click *OK*to close the dialog.

5.

   Alternatively,right-click inthe chart area and select*Insert
   **Legend*from the context menu to insert a legend in the default
   position on the right side of the chart.

6.

   Click outside the chart to leave edit mode.

To remove a legend from your chart:

1.

   Select the chart by double-clicking on it to enter edit mode. The
   chart should now be surrounded by a gray border.

2.

   Go to*Insert>Legend*onthe main menu bar to open the Legend
   dialog.

3.

   Deselect the *Display legend* checkbox.

4.

   Click *OK* to close the dialog.

5.

   Alternatively,right-click inthe chart area and
   select*DeleteL**egend*from the context menu.

6.

   Click outside the chart to leave edit mode.

Again, the user can add or remove, but not edit.

Another dead-end source illuminates another Calc problem:

https://ask.libreoffice.org/en/question/44705/making-a-double-line-graph-with-proper-data-in-libre-office-calc/ 



This question is “closed” because it was allegedly answered, whilst in 
fact no answer of any kind was provided to the basi

Re: [libreoffice-users] Calc calendar in xlsx file format - day numbers disappeared suddenly

[Remy Gauthier]

Hi,It is a bit hard to guess what is going on without actually seeing
the file. Can you e-mail it to me?Two things come to mind:-
Calculations are in manual mode and the cells are not re-calculated.
You can see the status in Data > Calculate (or try F9 to force
recalculation)- Conditional formatting that overrides the cell text
color because an entry is invalid (year greater than 2020?). You can
try to copy a blank cell and paste the value in the cell elsewhere
(using Paste Special). You can then see if the calculation is done and
if it's only a formatting issue.- A calculation or data element is
wrong and the cell formula prevents wrong outputs to be shown
(something like =IFERROR(something,"")). You can use the Detective
(Tools > Detective > Trace Precendents) to find which cells control
which and find the source issueI hope this helps.Rémy.
Le mardi 12 janvier 2021 à 15:27 -0700, Regina3000 a écrit :
> I have a calendar file in XLSX format that I've been using and
> tweaking foryears, now. For the past two days, I've been updating it
> for 2021. Today, Ithought I had it correct, but of course you always
> miss one thing oranother.
> I copy the base file and update it from year to year.
> I reopened it and all of the day numbers were gone. I opened the base
> file,and the day numbers were gone from it, as well.
> By "gone," I mean that they don't show. If I select a day number
> cell, I cansee the formula that generates the number, and if I go to
> edit the cellformat, and turn the font color from "Automatic" to
> "black," it STILLdoesn't show up.
> I'm guessing there is an issue with LibreOffice, but I don't
> haveLibreOffice running. I can't find it in Task Manager (running
> Windows 10Pro) to kill it.
> Has anyone seen anything like this, and if so, what did you do to fix
> it? Ican't reboot, right now, because I'm in the middle of a bunch of
> otherstuff, too, but I suspect that if I rebooted, it would be fixed.
> Thanks!
> 
> 
> --Sent from: 
> http://document-foundation-mail-archive.969070.n3.nabble.com/Users-f1639498.html
> 

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

[libreoffice-users] Calc calendar in xlsx file format - day numbers disappeared suddenly

[Regina3000]

I have a calendar file in XLSX format that I've been using and tweaking for
years, now. For the past two days, I've been updating it for 2021. Today, I
thought I had it correct, but of course you always miss one thing or
another.

I copy the base file and update it from year to year.

I reopened it and all of the day numbers were gone. I opened the base file,
and the day numbers were gone from it, as well.

By "gone," I mean that they don't show. If I select a day number cell, I can
see the formula that generates the number, and if I go to edit the cell
format, and turn the font color from "Automatic" to "black," it STILL
doesn't show up.

I'm guessing there is an issue with LibreOffice, but I don't have
LibreOffice running. I can't find it in Task Manager (running Windows 10
Pro) to kill it.

Has anyone seen anything like this, and if so, what did you do to fix it? I
can't reboot, right now, because I'm in the middle of a bunch of other
stuff, too, but I suspect that if I rebooted, it would be fixed.

Thanks!



--
Sent from: 
http://document-foundation-mail-archive.969070.n3.nabble.com/Users-f1639498.html

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc diagrams/figures

[Howard Barr]

Michael, thanks for the update. I was able to fix a very similar subscript
problem.

On Wed, 16 Dec 2020, 17:20 Michael D. Setzer II, 
wrote:

> On 16 Dec 2020 at 8:49, Informatique wrote:
>
> Date sent:  Wed, 16 Dec 2020 08:49:01 +0100
> From:   Informatique
> 
> To: users@global.libreoffice.org
> Subject:    Re: [libreoffice-users] Calc diagrams/figures
>
> > hello,
> > for these problems, i think that LO is not the best tools for scientific
> diagram.
> > Perhaps using a tool as GnuPlot will be better.
> > http://www.gnuplot.info/
> > François-Marie BILLARD
> >
>
> Did some messages directly with him that included images of what he
> was doing. Problem was with labels and not the actual diagrams.
>
> The labels where entered using the subscript or superscript ctrl keys, but
> when the graphs were showing, the sub and super options were gone,
> and the labels showed as regular characters. Found that if the sub/super
> characters were entered as unicode ctrl-shift-u then they would stay
> correctly. Unfortunately, not all characters have unicode for sub/super,
> but many do. Would have thought the unicode would have the 26 letters
> upper and lower case with both sub/super characters. Total of 104
> characters. But some don't seem to exist.
>
> The images he sent made it clear on what his issue was.
>
>
> > On Mon, 14 Dec 2020 16:55:00 +0100
> > Thomas Welz  wrote:
> >
> > > Hello together,
> > >
> > > first of all, I hope you can understand, what I want to say. I'm not
> > > really familiar with all the terms regarding this topic.
> > >
> > > I'm not sure if this is the appropriate way for improvement
> suggestions.
> > > I hope so. Sometimes I have to generate figures, which I need for
> > > studying and there's the issue: I need labels like "EEHD,ECD" or "U0"
> or
> > > "cm" etc (superscript and subscript characters)... But this is not
> > > possible either for labels or for the legend. Of course, I can do it
> > > with the drawing tool, but it takes a lot of time and does not change
> > > automatically if I change anything.
> > >
> > > This "feature" would be really awesome and progressive (I think MS
> > > Office Excel has same problem) for everyone who is studying sciences
> or
> > > engineering or similar.
> > >
> > > Kind regards,
> > > Thomas
> > >
> > >
> > > --
> > > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> > > Problems?
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> > > Posting guidelines + more:
> https://wiki.documentfoundation.org/Netiquette
> > > List archive: https://listarchives.libreoffice.org/global/users/
> > > Privacy Policy: https://www.documentfoundation.org/privacy
> >
> >
> > --
> > Informatique 
> >
> > --
> > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> > Problems?
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> > Posting guidelines + more:
> https://wiki.documentfoundation.org/Netiquette
> > List archive: https://listarchives.libreoffice.org/global/users/
> > Privacy Policy: https://www.documentfoundation.org/privacy
>
>
> ++
>  Michael D. Setzer II - Computer Science Instructor (Retired)
>  mailto:mi...@guam.net
>  mailto:msetze...@gmail.com
>  Guam - Where America's Day Begins
>  G4L Disk Imaging Project maintainer
>  http://sourceforge.net/projects/g4l/
> ++
>
>
>
>
> --
> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> Problems?
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy
>
>

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc diagrams/figures

[Michael D. Setzer II]

On 16 Dec 2020 at 8:49, Informatique wrote:

Date sent:  Wed, 16 Dec 2020 08:49:01 +0100
From:   Informatique 

To: users@global.libreoffice.org
Subject:Re: [libreoffice-users] Calc diagrams/figures

> hello,
> for these problems, i think that LO is not the best tools for scientific 
> diagram.
> Perhaps using a tool as GnuPlot will be better.
> http://www.gnuplot.info/
> François-Marie BILLARD
> 

Did some messages directly with him that included images of what he 
was doing. Problem was with labels and not the actual diagrams. 

The labels where entered using the subscript or superscript ctrl keys, but 
when the graphs were showing, the sub and super options were gone, 
and the labels showed as regular characters. Found that if the sub/super 
characters were entered as unicode ctrl-shift-u then they would stay 
correctly. Unfortunately, not all characters have unicode for sub/super, 
but many do. Would have thought the unicode would have the 26 letters 
upper and lower case with both sub/super characters. Total of 104 
characters. But some don't seem to exist. 

The images he sent made it clear on what his issue was. 


> On Mon, 14 Dec 2020 16:55:00 +0100
> Thomas Welz  wrote:
> 
> > Hello together,
> > 
> > first of all, I hope you can understand, what I want to say. I'm not 
> > really familiar with all the terms regarding this topic.
> > 
> > I'm not sure if this is the appropriate way for improvement suggestions. 
> > I hope so. Sometimes I have to generate figures, which I need for 
> > studying and there's the issue: I need labels like "EEHD,ECD" or "U0" or 
> > "cm" etc (superscript and subscript characters)... But this is not 
> > possible either for labels or for the legend. Of course, I can do it 
> > with the drawing tool, but it takes a lot of time and does not change 
> > automatically if I change anything.
> > 
> > This "feature" would be really awesome and progressive (I think MS 
> > Office Excel has same problem) for everyone who is studying sciences or 
> > engineering or similar.
> > 
> > Kind regards,
> > Thomas
> > 
> > 
> > -- 
> > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> > Problems? 
> > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> > Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> > List archive: https://listarchives.libreoffice.org/global/users/
> > Privacy Policy: https://www.documentfoundation.org/privacy
> 
> 
> -- 
> Informatique 
> 
> -- 
> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> Problems? 
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy


++
 Michael D. Setzer II - Computer Science Instructor (Retired) 
 mailto:mi...@guam.net
 mailto:msetze...@gmail.com
 Guam - Where America's Day Begins
 G4L Disk Imaging Project maintainer 
 http://sourceforge.net/projects/g4l/
++




-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Re: [libreoffice-users] Calc diagrams/figures

[Informatique]

hello,
for these problems, i think that LO is not the best tools for scientific 
diagram.
Perhaps using a tool as GnuPlot will be better.
http://www.gnuplot.info/
François-Marie BILLARD

On Mon, 14 Dec 2020 16:55:00 +0100
Thomas Welz  wrote:

> Hello together,
> 
> first of all, I hope you can understand, what I want to say. I'm not 
> really familiar with all the terms regarding this topic.
> 
> I'm not sure if this is the appropriate way for improvement suggestions. 
> I hope so. Sometimes I have to generate figures, which I need for 
> studying and there's the issue: I need labels like "EEHD,ECD" or "U0" or 
> "cm⁴" etc (superscript and subscript characters)... But this is not 
> possible either for labels or for the legend. Of course, I can do it 
> with the drawing tool, but it takes a lot of time and does not change 
> automatically if I change anything.
> 
> This "feature" would be really awesome and progressive (I think MS 
> Office Excel has same problem) for everyone who is studying sciences or 
> engineering or similar.
> 
> Kind regards,
> Thomas
> 
> 
> -- 
> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> Problems? 
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy


-- 
Informatique 

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy