Author Topic: A strange spreadsheet behaviour  (Read 2057 times)

0 Members and 1 Guest are viewing this topic.

Offline Keith

  • Hero Member
  • *****
  • Posts: 756
  • Karma: 11
  • Gender: Male
  • Linux Novice
    • View Profile
    • Awards
A strange spreadsheet behaviour
« on: September 18, 2015, 07:03:36 pm »
In view of the esoteric posts in this section of the Forum, I hesitate to ask such an apparently simple question - but here goes.

I am fiddling with some *.XLS spreadsheets using LibreOffice for a club and have found an odd behaviour.  The same thing happens if I create an *.ODS file, too. 

If I create a column of numbers such as 1,2,3,4,5, highlight them then drag the handle down the column, the series is extended as expected.
If I create a column of formulae such as =B1,=B2,=B3,=B4,=B5, highlight them then drag the handle down the column, the series is extended as expected - displaying the contents of cells B1 to B5. 

If I create a column of numbers such as 2,4,6,8, highlight them then drag the handle down the column, the series is extended as expected - in steps of 2. 
If I create a column of formulae such as =B4,=B6,=B8, highlight them then drag the handle down the column, the result is:
=B4,=B6,=B8,   =B7,=B9,=B11,   =B10,=B12,=B14,   =B13,=B15,=B17, etc. 
Which is weird - and not what I want.  I have tried it on a variety of machines using a Peppermint 3 laptop and a fresh install of Ubuntu 14 on a desktop.

Any ideas, anyone? 

UPDATE
Well, I think I may have cracked it!  The answer may be the use of indirect addressing.  It means creating another column, but that's no hardship. 
UPDATE 2
Indirect addressing does the job.  I found the info at https://wiki.openoffice.org/wiki/Documentation/OOo3_User_Guides/Calc_Guide/Address,_Indirect,_Offset,_Index.
« Last Edit: September 19, 2015, 08:28:03 pm by Keith »

Offline Mark Greaves (PCNetSpec)

  • Administrator
  • Hero Member
  • *****
  • Posts: 17351
  • Karma: 442
  • Gender: Male
  • "-rw-rw-rw-" .. The Number Of The Beast
    • View Profile
    • PCNetSpec
    • Awards
Re: A strange spreadsheet behaviour
« Reply #1 on: September 19, 2015, 09:20:45 pm »
Thanks for providing the solution to your own question Keith .. I hadn't a clue  :o
WARNING: You are logged into reality as 'root'
logging in as 'insane' is the only safe option.
pcnetspec.co.uk

Offline Keith

  • Hero Member
  • *****
  • Posts: 756
  • Karma: 11
  • Gender: Male
  • Linux Novice
    • View Profile
    • Awards
Re: A strange spreadsheet behaviour
« Reply #2 on: September 19, 2015, 09:30:12 pm »
Hi Mark. 

I keep discovering interesting features of Calc but the hard bit is using the right search criteria to find them.  Some of these commands are mind-bending to understand, but often worth the effort. 
Thanks to this Forum (and your generous help) I am getting better at solving my own problems, which is why I haven't been here for a while. 

Cheers!
    Keith

Offline Mark Greaves (PCNetSpec)

  • Administrator
  • Hero Member
  • *****
  • Posts: 17351
  • Karma: 442
  • Gender: Male
  • "-rw-rw-rw-" .. The Number Of The Beast
    • View Profile
    • PCNetSpec
    • Awards
Re: A strange spreadsheet behaviour
« Reply #3 on: September 20, 2015, 01:08:45 am »
I've always been the "I can make it work, but then don't ask me how to use it" type .. if LO is broken, I can help you get it working, ask me ANYTHING about using it and I'm clueless.

So if I ever find the need to do a speadsheet we'll be swapping places my mate ;)

I wonder how many car mechanics there are out there that can't drive  :o
WARNING: You are logged into reality as 'root'
logging in as 'insane' is the only safe option.
pcnetspec.co.uk

Offline donwatson

  • Hero Member
  • *****
  • Posts: 905
  • Karma: 8
  • Gender: Male
  • Getting older
    • View Profile
    • Awards
Re: A strange spreadsheet behaviour
« Reply #4 on: September 20, 2015, 12:25:16 pm »
Keith,
Many thanks for your own question and answer. I am stumbling along at the moment with databases and spreadsheets.
I find LO excellent to work with and a friendly crowd too but I have to use MS as well as that is what the sheep in this particular fold have been using for donkey's years. (where did all these animals come from ?).
I am much like you and keep discovering things about LO and try to make them useful. Best of luck.

take care
Don W
Novatech build
Intel Celeron CPU 1.80 Ghz   Memory 4.00GB (3.71GB usable)
Peppermint 7    64 bit     500 GB HD        496 GB Ext 4
4.0GB Swap Partition    4.0GB Extended Partition   1.1Mb Free Space

 


SimplePortal 2.3.3 © 2008-2010, SimplePortal