Just warning. Sorting destructs cell names binding. Content of cells changes its position (address), however, the table mapping names to addresses does not reflect this. This leads to reference errors and incorrect results.
Is there any way to avoid such an ambush?
Please describe exactly how you proceeded with the sorting.
Please step by step. Thank you.
If you mean that if you define single names for single cells those names are not reordered along with the sort then Iād say that is expected. You name the cell, not the value it contains. Gnumeric and Google do it the same. Does Excel?
OK. However, please note that I have a Polish version of Calc, and some words may be distorted.
In column āAā, I had words that served as the sort key. Column āBā had values in named cells.
I selected position āDataā from the main menu and the āSort ā¦ā.
In the dialog window named āSortingā in drop-down list āSorting key 1ā I selected āColumn Aā.
It seems less important, in the options I checked, the āRange contains column labelsā box.
After pressing āOKā the sheet was sorted correctly. I mean, Column āBā followed changes in column āAā. But addresses in table of names did not change. I inspected it by drop-down the edit field with cell addressess/names. After expanding, there is the first item āManage Names ā¦ā Since no changes were applied to this table, all formulas containing names started to return incorrect results.
Do you have any further question?
- You can use the VLOOKUP function to lookup values in B by names in A.
- Define Label Range describes an almost unkown feature which does something similar.
- Like 90% of all spreadsheet users you try to use a calculator software as a database surrogate. The core functionality of spreadsheet software has nothing to do with items stored in records. All software for book keeping and alike is built around database software.
Yes. Sorting a range of cells in Excel does not affect the names of the workbook (worksheet) and does not change formulas in cells that refer to cells in the range being sorted.
Unfortunately, Google spreadsheet works just like LibreOffice calc.
Yes, of course.
Iām an experienced spreadsheet user, and I canāt imagine why you would want to sort the range names. You are doing something wrong. So formulate to us what you want to do in general, and we will suggest ways to solve the problem. Thatās the right way to do it.
Your question will go away by itself.
I think @Villeroy said the same thing.
1 Like
It was not my intention to sort the range names. During that time I sorted named cells in order to facilitate visual search. And that destroyed most of the formulas in my document.
I have checked @Villeroy advice to Define Label Range. I would almost have what I meant. But here copying a formula moves the reference and changes the label name. It works similarly, to how copying does with relative addressing. Is there a way to keep the label permanent when copying a formula (as if the $ sign was used in the address)?
General rule: formulas must not refer to any other cell in the range than the cells of the current row (we are talking about data, not common constants outside the range). You must work with a record, that is, a tuple. Show by example what you have and what you want to get. Weāll help. Unfortunately, I donāt fully understand you.
I never use this checkbox.
@eeigor . Thank you for your help showing me the potential solution. For me, the concept of @Villeroy with the VLOOKUP function seems perfect.
Here are more details to deepen the problem with the āDefine Label Range.ā In a separate sheet, I have ingredients with their nutritional value. Thatās where I defined āLabel Range.ā Now elsewhere, Iām designing lunch with labels. If I duplicate this project, the references below change, and I have a different āLunch.ā
Without all that spreadsheet madness: http://forum.openoffice.org/en/forum/download/file.php?id=8641
Itās just a suggestion. It works for big data. Maybe in your case, you can bend the rules and make things simpler and clearer. But think about my solution.
Try to arrange data in a different way: row by row (by records). Then the names of the fields will be: LunchNo, IngredientName, IngredientValue. Such a range will contain repetitive data, for example, LunchNo for each ingredient in it. Such data is easy to filter, process in a pivot table, etc. But if you donāt have enough data, letās do something elseā¦
Updated:
data-storage.ods (14.6 KB)
Edit:
You work with a spreadsheet like a sheet of paper. That is, keep the data in a form that is easy to perceive (print). Unfortunately, the format of data storage is different from the format of data showing (printing). The first is one, and it must be efficient (as convenient to the computer), the second can be presented in many different forms (as convenient to humans).
1 Like
Give an example of the āLabel Rangeā in a separate sheet.
@eeigor: For your curiosity, I enclose an excerpt from the Label Assortment. When copying cells from Lunch1 to Lunch2, the value 6.4 from the label āMakaronā changed to 5.12 because the label automatically changed to āMakowiec.ā The same happened with the second ingredient.
Thank you for your assistance - I learned a lot. My main goal is to keep the solution simple for my use. The task is not of the āBig dataā class 
I attached a file (data-storage.ods) above. See & try.
You donāt need to copy anything, you need to select using the drop-down list (data validation). You donāt need to sort, you need to filter. And donāt forget to copy the format when adding a new row to the lunch data, and to copy and paste the formula in column C (IngredientValue).
C6: =VLOOKUP(B6;Ingredients;2;0)
This is where the formula VLOOKUP() mentioned by @Villeroy comes in handy. Everything fits together. Your problem has evaporatedā¦
Edit:
And donāt forget to correct the references to the two named ranges, or expand the ranges at once, so that there are more rows at the bottom. But in this case the drop-down list will contain empty lines.
1 Like
@antekg0, try this file. The references are calculated automatically. A pivot table is added (auto refreshed by a macro).
data-storage (1).ods (56.5 KB)
Updated.
Example extended. Who cares.
As a matter of course, data collections like recipes belong into a database, particularly when relations are involved.
diet.odb (20.9 KB)