I am a newbie to Calc but I do have experience with VBA, albeit specific to MS Access only. I don’t have much experience with Excel either. Anyways, I am looking to avoid going down a blind alley if there is an obvious solution to my problem.
I am using Calc to track my nutrition. I have created 3 sheets in a Calc document. The first tracks data for foodstuffs, like so:
Note the units of measurement are grams/ounce.
The second is where I log what I eat, with the date, foodstuff and amount in ounces. I have already set it such that I can only enter foodstuffs that have a corresponding entry in the first sheet:
| Day | Food | Amount |
|---|---|---|
| 08/29/21 | Oats | 1.5 |
| 08/29/21 | Yogurt | 3.5 |
| 08/29/21 | Peach | 5.3 |
| 08/29/21 | Ham | 1 |
| 08/30/21 | Milk | 8 |
Now, I want a third sheet that summarizes the calories, proteins, etc by date. The way I would imagine doing this would be to loop through every row in sheet 2, then look up the nutritional info for the foodstuff within sheet #1 ,and then calculate the total calories,fat,protein, and carbs for that row.
For example, if I ate 3.5 oz of Yogurt on 8/29/21, that would be 3.5g protein, 4.9g carbs, 3.5g fat, and 65.1 calories. Now, I do this for every row and then aggregate by date, such that the third sheet has rows headers like so:
| Calculated Totals | ||||
|---|---|---|---|---|
| Date | Calories | Protein | Fat | Carbs |
To sum up, I see the following that tasks that need to be automated:
(1) look up foodstuff
(2) calculate nutrition per amount of foodstuff
(3) aggregate calculations per day (or week, month…)
According to my research, (3) is a good candidate for a a pivot table but considering the other tasks and the fact that I don’t particularly need the intermediate numbers this could conceivably be accomplished through LibreOffice Basic functions doing everything behind the scenes. On the other hand, it wouldn’t hurt anything to have the intermediate numbers visible either, if this be done completely by chaining built in commands.
I’d like it be done automatically whenever sheet #2 gets a new row.
I have discovered the Pitonyak book on macros, and I am going through it even although OOo 3.x appears to be somewhat divergent from my platform (LibreOffice 7.0.4).
What is the best way forward?
