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…!
(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=""
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.
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.
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 @mariosv works for you, you should prefer it. I will nonetheless delete this one.)
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.
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)
If you prefer macros-based solutions, then you can use the macro FilllDown, which 12 years ago was written for you by Villeroy
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.