How to copy data only to empty cells

Discuss the spreadsheet application

How to copy data only to empty cells

Postby Hereticus » Sun Jan 13, 2008 12:51 am

Please, anyone could explain to me how to past a formula only into empty cells within a column?
I mean, with Excel I simply apply filter (empty) then copy formula (or data) to the whole (blank) column, and this does not overcopy other cells (the hidden not-empty cells).

If I try to do the same with Openoffice Calc with the same method all hidden not-empty cells are affected.

I only want to copy inside empty cells, skipping the other ones...

Any idea?

Thanks
Hereticus
 
Posts: 2
Joined: Sun Jan 13, 2008 12:38 am

Re: How to copy data only to empty cells

Postby TerryE » Sun Jan 13, 2008 4:30 am

Well step one is to read the survival guide and this tell you to search for existing solutions. Try a search for "empty cells". You will see that one solution is to turn the problem on its head.

Note that Calc doesn't allow you to paste into multiple selection ranges anyway. The only alternative would be to write a macro to (say) copy the cotents of the top LH cell into any blank cells in the range.
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
TerryE
 
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: How to copy data only to empty cells

Postby Hereticus » Mon Jan 14, 2008 11:48 pm

Onestly, the workaround that I have found with the search is simply... impracticable... too much time for a so simple thing...
I hope that filters will be improved in future releases, or at least that in the copy function there will be an option to skip "not-empty cells"
Is there a place to suggest these things to programmers?

Thank you for your reply

Aldo
Hereticus
 
Posts: 2
Joined: Sun Jan 13, 2008 12:38 am

Re: How to copy data only to empty cells

Postby Villeroy » Tue Jan 15, 2008 6:33 am

You can register at qa.openoffice.org and vote for issue 33851 http://qa.openoffice.org/issues/show_bug.cgi?id=33851
Meanwhile you may try this set of macros: http://sourceforge.net/project/showfile ... _id=188206
Contrary to Excel they preserve all kinds of non-visible cells (filtered and hidden) to be overwritten by fill-operation to any direction. You may replace the original menu items Edit>Fill>Down|Right|Up|Left with the first set of macros. With visible cells alone the macros do just the same as the original commands.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 19545
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 11 guests