No announcement yet.

OT, spreadsheet question from an infrequent user...

  • Filter
  • Time
  • Show
Clear All
new posts

  • OT, spreadsheet question from an infrequent user...

    I'm using Open Office, free download.

    I'm pasting in three columns of data, might be as much as 300 sets (rows). Calculations yield three new columns of the same length and I copy the new columns of data out for another use.

    In order to copy I select the new data set by dragging from the top left down to bottom right. Works fine.

    But, I wonder if there is some way to have the newly calculated columns be automatically selected in the calculation process to I could simply say "copy" from the edit menu to put them on clipboard. The dragging selection method seems cumbersome.

    Thanks for looking.

  • #2
    Once you have selected the cells you want, click Ctrl-C to copy. Then put the cursor where you want the copy, and click Ctrl-V to paste.

    You can also use macros to automate this, but I haven't done that for quite some time.

    Paul , P S Technology, Inc. and MrTibbs
    USA Maryland 21030


    • #3
      I'm not familiar with open office specifically - but I presume you can name ranges. In the upper left hand corner of excel the selected block of text is denoted by the upper left corner cell of the selected block, or the active cell where your cursor is located, for example if you select d1:e300 the header tab will display d1. You can also just type d1:e300 in that block and excel will select that entire block without having to drag anything - then you can copy it or sort it or whatever you want to do with it.

      You can also select the block and type in a name for the range in that tab and the spreadsheet will always associate that set of cells with that name. You can just go to that tab and enter that name and it will select the cell range for you.


      • #4
        Hi DR
        Not quite what you are looking for, but click the top left cell of the array you want to select. Then use the slider to scroll to the end of
        the array. Press the shift key and click on the lower right cell of the array. The Entire range is now selected.
        Location: Long Island, N.Y.


        • #5
          I use Excel but I see that Open Office has a Record Macro function so it should be dead easy to do what you want. You start the record macro function then you just manually perform the actions you want the macro to perform then click the stop recording button. Then you insert a button in your spreadsheet and assign the macro to it. Every time you click on the macro button it will perform the pre-recorded macro. It's a very powerful feature for anybody that regularly performs repeated operations and is well worth the effort of learning how to do it. Once you know how, it takes just a few seconds to record and assign a macro, it's not a major programming exercise.

          You could just as easily write a macro for the complete copy, paste, calculate, copy paste process.



          • #6
            If the number of rows is always the same in your result columns, then a recorded macro would be easiest. If it's variable, the only way to really make it automatic that I know of would be to program it (another type of macro), but I don't know if open office allows that in the way that you could with Excel and "visual basic for applications" that's built into it. I use LibreOffice and it does have that capability. It would show up under macros if it's there , but then you have the catch of having to learn how to do it. I might be able to help you out there if you need it.


            • #7
              Open office has Basic programming built in.
              Location: Long Island, N.Y.


              • #8
                Thanks all. I got some good ideas on how to make it easier than the drag selecting process.

                I may not have made it clear, every time I paste raw data in the number of rows varies. The number of columns is always three. The output is four columns with the same number of rows as pasted.

                I have Excel, but was a little overwhelmed by the features and options. Then I downgraded to an old version of Microsoft Works spreadsheet. That didn't do it, so I went with Open Office. I used to do lots of spreadsheet work for math calculations, that was fifteen or twenty years ago and my memory isn't that long although spreadsheeting is coming back to me.

                Anybody remember VisiCalc? A friend I worked with found an error in one of their higher math algorithms and received a thousand buck prize.

                Thanks again.


                • #9
                  I remember the MSDOS version of Lotus 123, and I used a shareware knockoff called AsEasyAs. And I also had (probably still have) a copy of Borland Paradox. But I used AsEasyAs for most of my MSDOS days, until I finally got Win95 and used Excel. I had a complete Microsoft Office 97 suite that I got cheap and used for many years, and now I use Open Office Calc, although I sometimes use the free Microsoft Excel to open XLSX files, which I convert to XLS or ODS. I now use Microsoft Access 2007 for heavy duty database work, including some VBA programming. In my MSDOS days I used a dBase shareware program called WAMPUM. My first computer was an IBM 7094 at JHU, using punch cards. And I also had/have a Sinclair ZX80 which I think also had a very simple spreadsheet app.
                  Paul , P S Technology, Inc. and MrTibbs
                  USA Maryland 21030


                  • #10
                    I believe the 'counta() function will count the number of cells with data. So if your 3 columns for example are d,e,f and you're not sure how many rows of data you have you could take a cell someplace, say g1 and enter '=counta(d1:d500)' and it will come back and tell you how many cells have data. If your column of data is continuous no blank cells in there, and is numbers or text it should tell you how many rows of data you have (just make sure the d500 is certainly bigger than the true number). Then if it comes back with 300 you'd know you have 300 rows of data. You could then select the block of data it by entering d1:f300 in the active cell block.