Results 1 to 7 of 7

Thread: Help with Libreoffice Calc

  1. #1
    Join Date
    Jun 2008
    Location
    USA
    Posts
    1,128

    Default Help with Libreoffice Calc

    Using Tumbleweed and the latest update of Calc. I have a spreadsheet with some columns that repeat one of a dozen or so entries, like last name or phone. Other columns have unique entries. I would like to have some sort of drop down list populated with names and phone numbers in the respective columns, so that I could click on an entry instead of typing them in all the time. The autocomplete is an imperfect solution since I might have entries for Charles, Chuck, Charlie, Chauncey, Charlotte, Charlotta etc. At the same time, I would need to ignore the drop down list to enter Bob etc.

    Look at my avatar. 40 years ago, I knew how to do this, but don't remember now. Does it involve a database? Should I skip Calc and make something in Base? Any advice is appreciated.
    Any sufficiently advanced technology is indistinguishable from magic. - Arthur C. Clarke

  2. #2
    Join Date
    Jun 2008
    Location
    San Diego, Ca, USA
    Posts
    11,403
    Blog Entries
    2

    Default Re: Help with Libreoffice Calc

    Been a long time I've dealt with spreadsheets in this way,

    I can't remember ever creating what you describe except by programming (scripting cell objects).

    I remember in Excel and I assume should be the same in Calc that if there is a particular pattern of content in a series of cells I wanted to replicate, I'd select the group of cells that show the pattern, then grab the handle of the group and drag in a particular direction. The pattern would then replicate across the cells i dragged.

    Dropdown selections would be created by entering a code-behind mode of the cell, specify the object I wanted to create and then populate.
    I suppose if you created such an object, you could paste it into any cell (or series of cells) and then you'd have that capability in every cell.
    In a suite of applications like Libreoffice, the same methods should work in any of the individual applications (This is partly because of the OLE framework so so what you can do in Calc is probably similar or same to what you'd create in Base, and if you created in one app you could probably embed that into another app.

    TSU
    Beginner Wiki Quickstart - https://en.opensuse.org/User:Tsu2/Quickstart_Wiki
    Solved a problem recently? Create a wiki page for future personal reference!
    Learn something new?
    Attended a computing event?
    Post and Share!

  3. #3
    Join Date
    Oct 2014
    Location
    Italy
    Posts
    1,674

    Default Re: Help with Libreoffice Calc

    Hi Prexy, having "AutoInput" checked you can cycle through matching text in the same column by CTRL+TAB and reverse-cycle by CTRL+Shift+TAB.
    So by typing "Ch" in a new cell you can cycle trough Charles, Charlotta, etc. and of course complete your text with a new entry if needed.
    Hope this helps.
    Main: Leap 15 Gnome on i7 4720HQ + Geforce GTX960M
    Test: Leap 42.3 (& others) on Core2Duo + GM965

  4. #4
    Join Date
    Jun 2008
    Location
    USA
    Posts
    1,128

    Default Re: Help with Libreoffice Calc

    Quote Originally Posted by OrsoBruno View Post
    Hi Prexy, having "AutoInput" checked you can cycle through matching text in the same column by CTRL+TAB and reverse-cycle by CTRL+Shift+TAB.
    So by typing "Ch" in a new cell you can cycle trough Charles, Charlotta, etc. and of course complete your text with a new entry if needed.
    Hope this helps.
    Please forgive the idiocy.
    This tip works perfectly. Except, I was using Google Sheets, NOT LibreOffice! Please see my avatar.

    I tried this on Google Sheets and all it did was change my browser tab! That is also a useful tip; just not for this issue.

    Thank you for your help. Sorry that I raised a false alarm.
    Any sufficiently advanced technology is indistinguishable from magic. - Arthur C. Clarke

  5. #5
    Join Date
    Jun 2008
    Location
    San Diego, Ca, USA
    Posts
    11,403
    Blog Entries
    2

    Default Re: Help with Libreoffice Calc

    Quote Originally Posted by Prexy View Post
    Please forgive the idiocy.
    This tip works perfectly. Except, I was using Google Sheets, NOT LibreOffice! Please see my avatar.

    I tried this on Google Sheets and all it did was change my browser tab! That is also a useful tip; just not for this issue.

    Thank you for your help. Sorry that I raised a false alarm.
    I imagine there should be a way to do your work in Libreoffice Calc,
    Then if you want it in Google Sheets,
    You should be able to export/import into Sheets.

    TSU
    Beginner Wiki Quickstart - https://en.opensuse.org/User:Tsu2/Quickstart_Wiki
    Solved a problem recently? Create a wiki page for future personal reference!
    Learn something new?
    Attended a computing event?
    Post and Share!

  6. #6
    Join Date
    Jun 2008
    Location
    Groningen, Netherlands
    Posts
    20,000
    Blog Entries
    14

    Default Re: Help with Libreoffice Calc

    Quote Originally Posted by Prexy View Post
    Using Tumbleweed and the latest update of Calc. I have a spreadsheet with some columns that repeat one of a dozen or so entries, like last name or phone. Other columns have unique entries. I would like to have some sort of drop down list populated with names and phone numbers in the respective columns, so that I could click on an entry instead of typing them in all the time. The autocomplete is an imperfect solution since I might have entries for Charles, Chuck, Charlie, Chauncey, Charlotte, Charlotta etc. At the same time, I would need to ignore the drop down list to enter Bob etc.

    Look at my avatar. 40 years ago, I knew how to do this, but don't remember now. Does it involve a database? Should I skip Calc and make something in Base? Any advice is appreciated.
    Click, and then what? To search? A form to display all the data for one object?
    ° Appreciate my reply? Click the star and let me know why.

    ° Perfection is not gonna happen. No way.

    https://en.opensuse.org/openSUSE:Board#Members
    http://en.opensuse.org/User:Knurpht
    http://nl.opensuse.org/Gebruiker:Knurpht

  7. #7
    Join Date
    Jun 2008
    Location
    USA
    Posts
    1,128

    Default Re: Help with Libreoffice Calc

    Here's what I ended up doing:

    I continue to use Google Sheets. I selected the cell at the bottom of one of the columns that I was trying to "automate." From the menu, I selected Data and from the submenu that popped up, I selected Data Validation.

    In the box that popped up next, I clicked on the box that said "Criteria" and switched to the sheet that had my list of names. I selected almost all of the column with names in it, then clicked "Save."

    The cell that this pertained to now had a drop-down arrow. Clicking that arrow pops up a box with all the names in it. I can select the desired name by clicking on it. This pretty much solves the issue for me. It would be better if the list of names were alphabetized and duplicates were eliminated. I guess I can do that by copying the names to another sheet and editing them to be the way I want, then reassigning the data validation to the new column of names.

    I used Google Sheets for all the obvious reasons. Now I'm thinking it might be faster to use LibreOffice and export to Google Sheets, as suggested here.

    Thanks again for the help.
    Any sufficiently advanced technology is indistinguishable from magic. - Arthur C. Clarke

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •