A have a nutrition speadsheet with basic ingredients in the top section and meals (that pull in data from these ingredients) below. If I do a Sort on the ingredients the new ingredients line numbers don’t get reflected into the meals. Is there a way around this?
Here’s an extract as a demo. If I use data/sort the meal picks up the wrong ingredients. Tried various sorts, same problem.
Nutrition-demo.ods (24.6 KB)
I’d say the usual way is to “find” the right line/record via VLOOKUP()
Are you adding new ingredients below the top section? If yes, I suggest to insert new lines in the top section, and add the new ingredients there.
Can you share a reduced sample file? At least three ingredients, and a meal with two. Thanks.
Please, edit your question to add more information (or the sample file). Don’t use Answer nor Sugges a solution.
OK, demo file now added.
There is an Option addressing exactly your issue:
>Tools>Options>LibreOffice Calc>General>>Input Settings>Update references when sorting range of cells (Enable)
However, it’s global and comes with disadvantages which were disussed many years ago in the bug tracker when the respective behaviour weas introduced. I woul never enable that option.
That option was introduced then and by default set to disabled.
Well, I’d never have found that check box! Thanks!
Anyway I enabled it, did a sort on the demo file and the meal sums still work fine. So I think I’ll enable the option, do the sort on my work file, then disable it again as there seem to be some issues. Sensible?
diet.odb (26.8 KB) [fixed, 3rd version]
Download and open the document, open the input form therein.
The input form stores recipe names in the yellow table, ingredients of the selected meal in the green table (including a quantity factor).
The upper white boxes show the nutrition values of a recipe, the boxes right of the ingredient list shows the details of the selected ingredient.
2025-01-21: 3rd version fixed another bug in the form (too hasty, too late in the evening).
The form has 5 sections now:
I added a simple report as well. It presents all meals with ingredients, values and sums of values in a printable Writer layout.
Thanks - I’ve never used the database facility but this project could be my way in. Will take some time I think but long overdue.
My Base document is ready to use without understanding anything, providing a lot more functionality and clarity than your sheet. Spreadsheets fail miserably with this task.
Before you can select an ingredient from a list box in the green table, you need to enter the name and its nutrient values into the table “INGREDIENTS”.
Just one thing: Before shutting down or hibernating the computer, close the form and the database in order to write everything to disk. Everything is saved automatically record by record, but the entire database may not survive a shutdown of the operating system.
I am forewarned!
Please use the updated version in my answer. I replaced the attachment. Now the default values for any new ingredient’s are zero and the labels of calories and protein values where interchanged in the bottom right details form.
2025-01-21: nother update with a bug fix and additions.
ok, got it!