Paste only to visible cells

English
#1

Is there a way to skip hidden cells when pasting in calc?

I have two spreadsheets sharing a number of column headers, lets call them Header1/Header2/Header3 etc. In the first one, I have an extra column for formulas for each column header, i.e. it goes A:Formula/B:Header1/C:Formula/D:Header2 etc. I want to paste the contents of the spreadsheet 2 into spreadsheet 1 in a way that the formula columns in sheet 1 are not altered. For this, I try hiding the formula columns in sheet 1 for I think this is the way to do it in ms excel. But I cannot find a way to paste the sheet 2 content by skipping the hidden columns in Calc. Copy only visible cells extension lets you copy from but not paste into only the visible cells.

#2

How about moving to the right, outside the data columns, all the formula columns? In this case, the problem with importing external data will be solved very simply (yes, it’s import, I didn’t make a reservation - this is an alternative to copy-paste)

#3

Not sure if I get what you suggested right, but the formulas need values from the succeeding columns. They don’t work if I move them. I didn’t know about importing though. I’ll check it out now, Thanks!

#4

Formulas keep on working when you move referenced cells. Keep all constant values on one side and all formulas on the other side. The references will follow.
You can drag a cell range with the mouse and when you drop it while holding the Alt key, no data will be overwritten because new cells will be inserted.

1 Like
#5

Thanks for the suggestion, but I need to keep the layout as it is. There are 50+ data columns, I need to be able to see both the data column and the associated results of the formulas side by side when I’m working with the file. I didn’t mention this in the original question, but if moving columns worked for me, then skip pasting would not be necessary in the first place.

#6

Well, you can try another option - create an additional, auxiliary sheet where you will insert new data. And on the sheet whose layout you don’t want to change, replace the values with cell references in this auxiliary sheet. In column A =IF($auxiliary.A1="";"";$auxiliary.A1), in column C =IF($auxiliary.B1="";"";$auxiliary.B1) and so on

2 Likes
#10

This workaround would do it. Thanks. So in order to make transferring data easier, I’ll have to prepare a template, either an auxiliary sheet like you suggested, or one with empty columns to make data columns in two sheets align. For the moment, I just added empty columns into the source spreadsheet to make the layout identical. I guess the answer to my question proper is that there is no way to skip hidden cells when pasting in calc. Pity, it’s such a small feature, and would come in handy in a lot of situations.

#7

https://extensions.libreoffice.org/es/extensions/show/copy-only-visible-cells

#8

Seems euphrates has checked this before asking.

1 Like
#9

Sorry, my mistake.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.