Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

For example:

Cell A1 = 2/3/2012
       A2 is empty
       A3 is empty
       A4 = 3/7/2012
       A5 is empty
       A6 is empty
       A7 is empty

What I want to do is:

  • select the complete list (A1 to A245),
  • take some action (e.g., click a button) that will automatically select only blank cells,
  • enter a formula (or a constant),
  • press Alt+Enter

and have the blank cells filled with that value.

But this doesn't happen.

  1. I can't select all the non-empty cells.
  2. Even if I select the non-empty cells manually, I can't fill them with Alt+Enter; the error message is displayed as "Insert into multiple selection not possible" (LibreOffice 3.6).

I have to use this feature for audit. (It can be done in Microsoft Excel by the "Go To" function, then select 'blanks').

share|improve this question

6 Answers 6

What you can do is the following:

  • Select an empty adjacent column, let's say B (insert a new column if you have data in B already)
  • In the B1 enter =IF(A1,A1,<your formula or constant>)
  • With B1 still selected press Ctrl+Shift+End then Ctrl+D to copy this formula for the rest of the column

The column B should have the data you were looking for

share|improve this answer

This is a lot more difficult in LO than in Excel! Even using Find Replace with Regular Expressions with ^$ doesn't work.... There is a solution, but it's not elegant.

Select the whole area (cells with content and blank cells, ex : A1:A50)
Ctrl+C
Select the area from 1st blank cell till the end, ex : A3:A50
Press F2, then write =A2 (or A49) 'cell above cell with focus
Alt+Enter
Select again the whole area
Ctrl+Shift+V
Check 'text', 'number', may be 'format' (what you want to copy) and 'skip empty cells'.

Found this at: OpenOffive Forum

share|improve this answer

recently I have to faces the same problem.

Thanks to Alex P I solved my problem.

in cell B1 enter

=IF(A1="A1","2/3/2012", IF(A1="A2","empty", IF(A1="A3","empty",IF(A1="A4", "3/7/2012", "do not match"))))

the recursion will be very very deep cause your have 200+ values in cell 'A1`

share|improve this answer

In LibreOffice 4.0 you can do a search and replace:

  1. select the area in which you want to fill empty cells
  2. Edit > Search & Replace
  3. search for nothing but check “[x] Entire cells“ and if you have done step 1 (selection) check also in “More options” “[x] current selection only”
  4. insert into the field of replace the text you want to replace

This may also work in previous versions of LibreOffice.

share|improve this answer

I don’t have access to LibreOffice Calc (or OpenOffice), but I know that, in Excel, you use Ctrl+Enter to enter a value into multiple selected cells.

Does Calc have a “Go To” function?  What happens when you try to use it?

share|improve this answer
    
The question as about LibreOffice. –  Paulo Fidalgo Jul 23 '13 at 14:22
    
@PauloFidalgo: Yes, I know. That’s why I said, “I don’t have access to LibreOffice …, but …, in Excel, …” –  Scott Jul 23 '13 at 16:15

Upgrade to LibreOffice 4:

Then you'll find under the Edit menu, a Fill sub-menu, and under that, an item called Down; this gives the contents of the first cell selected to all the other cells.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.