Ask Your Question

Calc: completing text columns to make page sortable

asked 2018-05-07 14:05:04 +0200

philreccomm gravatar image

updated 2018-05-07 22:27:32 +0200

LibreTraining gravatar image

I have a simple problem. We have a column of names and of reference numbers that are only mentioned in one row though they relate to places etc that occur in a series of rows. This means the page cannot be sorted. I want to extend the names etc down through the cells below them up to the next name. Of course, I could select and drag the corner, but there are lot of entries in many different sheets and files. It has been suggested to me to use an IF formula along the lines of =IF (A2=NULL, C1, A2), so I could obtain the required list in another column and then move it. The only output I get with this kind of formula is #NAME? I have tried ISBLANK in place of NULL. What am I doing wrong? Attached is a screenshot of the page as is. Thanks in advance...!image description

edit retag flag offensive close merge delete


(I don't understand the explanation. I would need a demonstrating sheet instead of it and instead of an image.)
If you get the error #NAME? this is because Calc didn't find a known name in a place where it expected one. If you actually used NULL in a comparison this should be expected. NULL is not a defined name in Calc. To test A2 for being blank use ISBLANK(A2).
To allow for cells being blank or having the empty string returned by a formula use the condition A2=""

Lupp gravatar imageLupp ( 2018-05-07 23:12:07 +0200 )edit

Many thanks for your comment @Lupp. I will look carefully at this.

philreccomm gravatar imagephilreccomm ( 2018-05-10 13:20:52 +0200 )edit

3 Answers

Sort by » oldest newest most voted

answered 2018-05-07 22:40:23 +0200

m.a.riosv gravatar image

Taking the data on a pivot table, showing all fields on rows and select Identify categories and repeat label names for the field where it is needed.

image description

edit flag offensive delete link more


I am working with a similar spreadsheet, and I think I got most of your explanation. But I can't figure out how to get to the dialog box where you show the "Repeat item labels" option. The name of the dialog box is cut off.

Edit: Never mind, I found it. For anyone in the future who wonders about this, you get to this dialog by double-clicking on the name of the entry in Pivot Table Layout, then clicking on the Options button.

Liberty Belle gravatar imageLiberty Belle ( 2018-07-21 21:14:34 +0200 )edit

answered 2018-05-08 09:50:51 +0200

JohnSUN gravatar image

You will need one simple formula and a dozen keystrokes

(I thank my dear colleague @Lupp for the sample of the data for this demonstration) Quick Fill Data Down.gif

If you prefer macros-based solutions, then you can use the macro FilllDown, which 12 years ago was written for you by Villeroy

edit flag offensive delete link more


Many thanks for all that work, @JohnSUN. Unfortunately I use Mac and the various commands failed to work in the way suggested. However, the tip for the DragQueen macro was excellent and that worked fine.

philreccomm gravatar imagephilreccomm ( 2018-05-10 13:14:01 +0200 )edit

answered 2018-05-08 00:16:58 +0200

Lupp gravatar image

updated 2018-05-08 00:21:46 +0200

You seem to use a spreadsheet mainly for keeping data. This may be seen as a task for a database application.
Some experienced users would talk of a spreadsheet misuse.

However, if you decide to use a spreadsheet for data keeping, you should regard the most basic principles applying to databases nonetheless. Regarding the example as far as I understand it, the most fatal breach is the different structure of the rows. One row, one relation! A field must either belong to all the rows or to none.

A roughly sketched transformation you find it this attachment.

(Editing: I was distracted, and when I finally posted the answer the other one had arrived. If the answer by @m.a.riosv works for you, you should prefer it. I will nonetheless delete this one.)

edit flag offensive delete link more


Thanks for your comments. The situation is that data was entered each year in a separate spreadsheet for many years in this unsortable fashion. I need to combine all those years into one sortable sheet to move forward. I did not have a say in how it was originally done. But a large number of people I have met have absolutely no idea how to use a spreadsheet much less a database. One person complained to me how much time it took her to type in the same name in cell after cell in a column.

philreccomm gravatar imagephilreccomm ( 2018-05-10 13:19:47 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-05-07 14:05:04 +0200

Seen: 51 times

Last updated: May 08 '18