Ask Your Question

jamesb's profile - activity

2019-09-05 18:23:55 +0200 received badge  Self-Learner (source)
2019-09-05 18:23:55 +0200 received badge  Teacher (source)
2019-09-04 12:14:26 +0200 commented answer Calc formula not appearing in clipboard or pasting into another sheet

Hi erAck See my "Further edit". Seems KRDC remote desktop connection client is breaking clipboard.

2019-09-04 12:13:48 +0200 edited answer Calc formula not appearing in clipboard or pasting into another sheet

So... The problem has gone away, but I don't know why! Here's what I did: 1 - Install Gnome desktop (I already had KD

2019-09-04 10:08:31 +0200 received badge  Famous Question (source)
2019-09-02 22:01:46 +0200 edited answer Calc formula not appearing in clipboard or pasting into another sheet

So... The problem has gone away, but I don't know why! Here's what I did: 1 - Install Gnome desktop (I already had KD

2019-09-01 07:15:11 +0200 received badge  Notable Question (source)
2019-08-31 17:39:53 +0200 answered a question Calc formula not appearing in clipboard or pasting into another sheet

So... The problem has gone away, but I don't know why! Here's what I did: 1 - Install Gnome desktop (I already had KD

2019-08-31 15:57:39 +0200 received badge  Popular Question (source)
2019-08-31 13:19:51 +0200 commented question Calc formula not appearing in clipboard or pasting into another sheet

Hi ve3oat Thanks for taking the time to reply. Yes, your suggestion would and does work fine. However, Ctrl-c should (

2019-08-30 22:28:13 +0200 asked a question Calc formula not appearing in clipboard or pasting into another sheet

Calc formula not appearing in clipboard or pasting into another sheet Having searched around I have only found answers t

2018-09-13 15:55:07 +0200 commented answer Summary from sheet with blank rows

Hi JohnSUN. Thanks again. As I said, all cells contain the identical formula, relative to their position in the sheet. N

2018-09-13 15:54:50 +0200 commented answer Summary from sheet with blank rows

Hi JohnSUN. Thanks again. As I said, all cells contain the identical formula, relative to their position in the sheet. N

2018-09-13 15:54:37 +0200 commented answer Summary from sheet with blank rows

Hi JohnSUN. Thanks again. As I said, all cells contain the identical formula, relative to their position in the sheet. N

2018-09-13 11:34:23 +0200 received badge  Popular Question (source)
2018-09-13 11:32:40 +0200 commented answer Summary from sheet with blank rows

Hi JohnSUN Thank you kindly for your reply. I'm most grateful. Although this method works for non-calculated data, it d

2018-09-13 11:31:50 +0200 commented answer Summary from sheet with blank rows

Hi JohnSUN Thank you kindly for your reply. I'm most grateful. Although this method works for non-calculated data, it d

2018-09-13 07:54:56 +0200 received badge  Popular Question (source)
2018-09-12 20:49:49 +0200 asked a question Summary from sheet with blank rows

Summary from sheet with blank rows Hi I have a sheet which produces results something like this: I'd like to display

2018-09-12 20:36:37 +0200 received badge  Notable Question (source)
2018-07-14 08:44:57 +0200 received badge  Famous Question (source)
2018-07-14 08:44:57 +0200 received badge  Popular Question (source)
2018-07-14 08:44:57 +0200 received badge  Notable Question (source)
2018-04-23 22:31:13 +0200 received badge  Notable Question (source)
2018-03-07 05:33:27 +0200 received badge  Famous Question (source)
2018-03-07 05:33:27 +0200 received badge  Notable Question (source)
2018-02-23 00:09:03 +0200 received badge  Popular Question (source)
2018-02-23 00:00:09 +0200 received badge  Enthusiast
2018-02-22 23:34:53 +0200 edited answer =MATCH confuses "AV." with "AVV"

Ah! Got it! =MATCH(T(B19),T(HL_download.$B$1:$B$202),0) It's the missing second T() modifier that I need. Stupid me!

2018-02-22 23:27:28 +0200 answered a question =MATCH confuses "AV." with "AVV"

Ah! Got it! > =MATCH(T(B19),T(HL_download.$B$1:$B$202),0) > It's the missing second T() modifier that I need. St

2018-02-22 23:24:13 +0200 commented answer =MATCH confuses "AV." with "AVV"

Thanks for this. =VLOOKUP(B19,HL_download.$B$1:$B$202,1,0) still returns the "AVV" and not "AV." entry. In my =MATCH

2018-02-22 22:35:53 +0200 asked a question =MATCH confuses "AV." with "AVV"

=MATCH confuses "AV." with "AVV" I'm using the following formula to look up a stock code and return it's line number:

2016-11-29 20:34:23 +0200 received badge  Popular Question (source)
2016-11-16 18:12:15 +0200 answered a question Hyperlink to Yahoo charts error following close and open of file - Calc v5.2

Hi Ratslinger

Thanks for taking the time to look at this. Oddly, even using your suggestion, the link fails to remain working between saves of the file.

Simply by removing the last bracket in the statement, and hitting enter (allowing Calc to correct the entry) the link then works fine.

I just can't fathom this out. I'm totally stumped!! I feel it's going to be something simple, but I can't find it...

:-)

2016-11-10 13:15:27 +0200 received badge  Organizer (source)
2016-11-10 13:12:46 +0200 asked a question Hyperlink to Yahoo charts error following close and open of file - Calc v5.2

Hi

Has anyone come across this behaviour in Calc?

  • Ensure macros are enabled
  • Create cell entry to a Yahoo thus:

=HYPERLINK(CONCATENATE("http://finance.yahoo.com/chart/",T(C18),"#eyjtdwx0aunvbg9ytgluzsi6zmfsc2usimjvbgxpbmdlclvwcgvyq29sb3iioiijztiwmdgxiiwiym9sbgluz2vytg93zxjdb2xvcii6iim5ntuyzmyilcjzag93u21hijp0cnvllcjzbwfdb2xvcnmioiijzjaxmjzmiiwic21hugvyaw9kcyi6ijuwiiwic21hv2lkdghzijoimsisinntyudob3n0aw5nijoimcisinnob3dfbweionrydwusimvtyunvbg9ycyi6iimxywm1njcilcjlbwfqzxjpb2rzijointailcjlbwfxawr0ahmioiixiiwizw1hr2hvc3rpbmcioiiwiiwibwzptgluzunvbg9yijoiizq1ztnmziisim1hy2reaxzlcmdlbmnlq29sb3iioiijzmy3yjeyiiwibwfjze1hy2rdb2xvcii6iim3oddkodiilcjtywnku2lnbmfsq29sb3iioiijmdawmdawiiwicnnptgluzunvbg9yijoii2zmyjcwmcisinn0b2nos0xpbmvdb2xvcii6iinmzmi3mdailcjzdg9jaermaw5lq29sb3iioiijndvlm2zmiiwicmfuz2uioii1esj9"),"Yahoo")

...where cell C18 contains the value "TCM.L" or any other stock ticker/code.

  • Check the hyperlink works - it does!
  • Save the file and open it again
  • Check the hyperlink again - it fails with

"Yahoo" is not an absolute URL that can be passed to an external application to open it.

Is there some limit to the hyperlink statement length? There seem to be 705 characters in it. What else might be going on?

I'm running CentOS7, Gnome desktop and LibreOffice 5.2.

Thanks! :-)

2016-08-16 22:37:14 +0200 received badge  Popular Question (source)
2016-08-16 08:21:19 +0200 received badge  Popular Question (source)
2016-08-15 15:16:26 +0200 asked a question Calc frequency array <-

In a frequency array I have the following:

0.00 | 6

-0.01 | 0

-0.02 | 3

←0.02 | 5

The last entry in the first column should read "<-0.02". Where do I stop "<-" becoming "←" on a per sheet basis? I don't seem to be able to find this out.

2016-08-14 20:02:11 +0200 asked a question Calc grid lines for sheet not saving

In one of my calc files I cannot save the setting of hide grid lines. This feature works OK in other files. I can work around this with:

Tools / Options / LibreOffice Calc / View / Visual Aids / Grid Lines: Hide

However, I'd be keen to know if anyone has encountered this before?

2016-08-14 19:45:45 +0200 commented question Formula becoming mashed during sheet copy

Strangely, today after opening my sheets afresh, the problem seems to have gone away. No. I can't figure it out either! :-)

2016-08-12 18:31:52 +0200 asked a question Formula becoming mashed during sheet copy

Help/About says I'm using LibreOffice 5:

Version: 5.2.0.4 Build ID: 066b007f5ebcc236395c7d282ba488bca6720265 CPU Threads: 2; OS Version: Linux 3.10; UI Render: default; Locale: en-GB (en_GB.UTF-8)

I wonder if anyone else has had this experience:

I have this formula in a cell in a sheet:

=MATCH(B2,'file:///home/jamesb/Documents/list.ods'#$'R&D'.B$1:B$1048576,0)

I click and make a copy of it with a new name further down the list of sheets, and the formula becomes mashed to:

=MATCH(B2,'file:///home/jamesb/Documents/list.ods'#$'R&D'.B$1:'file:///home/jamesb/Documents/list.ods'#$DefValue.B$1048576,0)

The sheets "R&D" and "DefValue" both exist in the remote file, and in that order. Is there something I've got wrong?

Thanks.

2016-08-12 01:34:53 +0200 answered a question File name link concatenation

Thank you so much, mark_t. That's where I was going wrong. Most grateful. :-)

2016-08-12 01:24:31 +0200 edited question File name link concatenation

Hello

In Calc, using the following I can, of course, call in data into my current spreadsheet file:

='file:///home/jamesb/Documents/List.ods'#$'R&D'.H22

However, I'd like to be able to reference the last two digits, "22", from another cell, say A1, in my current spreadsheet file, something like this:

=CONCAT("'file:///home/jamesb/Documents/List.ods'#$'R&D'.H",N(A1))

or

=CONCAT("=","'file:///home/jamesb/Documents/List.ods'#$'R&D'.H",N(A1))

Where the value in cell A1 is "22".

Unfortunately, as you'll know, this simply results in a cell displaying the following, but not the data contained in the remote file's cell:

'file:///home/jamesb/Documents/List.ods'#$'R&D'.H22

The method is eluding me, despite much searching.

I'd be most grateful if someone could help.

Thank you.

2016-08-12 01:11:33 +0200 received badge  Editor (source)