Apple Event: May 7th at 7 am PT
Is there a way to know which cell has been clicked on and becomes the active cell? I want a cell from another table to know that this cell was been clicked on so I can use the value.
MacBook Pro 17″, macOS 10.13
Wow, that's a tough one. Maybe in addition to or instead of things like fiber, sodium, sugar, fat, etc. you have columns for common ingredients that have the potential to cause problems (the various vegetable and seed oils, lecithins, gums, and other non-foods they put in our food). Put an X in the columns that apply, or use checkboxes.
Instead of doing a day as a single row, you could do one food per row like I did except make column A the date and Column B the meal. You can then categorize the table or make a pivot table from it to gather it up nicely. Below is a really rough idea of what I mean.
Pivot Table:
On the pivot table you can click on the triangles to collapse/expand rows. Option click will collapse/expand all rows based on that column. Below is after option-clicking on the triangle next to Breakfast:
I used "supper" instead of "dinner" because the pivot table puts it in alphabetical order (Breakfast Lunch Supper vs Breakfast Dinner Lunch).
Maybe something written below will help.
First screenshot is a section of my table of food items. It is on a sheet by itself. Nutrition data and serving size was from the package/can or from web sites. One row in column A is "-----".
Format column A as text when more items are to be entered. Enter the new items. Sort by column A. Select all data cells in column A. Format as pop up. Copy the one that is "-----". Paste it where you need it.
Second screenshot is one of my other tables with a short recipe in it (a simple banana/fruit smoothie). This is on a sheet by itself. The table for a day's food was just like this one, on its own sheet. I would have several of them and reuse them. Column A is pop ups. Column B is the servings. All other data is XLOOKUP multiplied by the servings in column B.
Copy/Paste Formula Results from row 2 to create a new food item in the food list. I kept an empty version of this table/sheet and that is where I would paste the new popups. I duplicated that sheet to create the others.
A popup will handle 250 items.
I have IBS and use my sheets to track foods I eat during the day to see if I can see a pattern the might cause my issues. The image below is where I put it all together. It is very wide so can't show the whole thing. This only show part of lunch, dinner & snacks. The description column I just start typing and pick from the list that pops up if I have already used. Otherwise I copy from my food list. The Fg & Sg uses Xlookup and the Qty I change if it is not 1 as that is the multiplier.
You can see in the food list and the Daily log that I changed my Names to include the size so am trying that out right now.
I am finding that I may need to figure out a way to look at ingredients in different foods that are more than one ingredient. Your last Ideas are giving me some thoughts on this. With IBS you have to look over the last 2 or 3 days to determine what might be causing your issues. Not easy as companies keep changing the ingredients. As you can tell I still need to do some work on this.
That's not possible via formulas.
It's theoretically possible with AppleScript but I don't think it will be practical.
What is your overall aim here? Perhaps there is another way to accomplish what you are trying to do.
SG
I have a spreadsheet with two tables to track food I eat and in particular the amount if fiber & sugar. The first table is the main table and the second is a list of foods with the fiber and sugar content. When I add a food in main table It looks up the fiber and sugar amounts and puts them in the cells for sugar and fiber in the main table. The table is quite wide and I am trying not to make it wider by adding another column for serving size. I was hoping to have a separate little table that would show serving size when I clicked on the food item for reference so I can adjust the quantity cell in the Main table without scrolling to the Food table. I hope this is clear and any ideas would be appreciated.
When I was tracking food, I put the serving size with the food name. "Carrot, Medium (61g)" , "Tomatoes, Diced, No Salt Added, 14.5oz", "Homemade Chili, (4 cups, 1 glass Tupperware container)" and so on. That first column was kind of wide but it was sufficiently descriptive of the items.
My other tables used a popup menu made of all the food "names" in my list of foods so it was easy to pick one. I had a few extra tables to create recipes in and would Copy/Paste Formula Results from them to create additional "Homemade" food items on my list.
I like what I see in the pivot table. Never used a pivot table so need to do a bunch of reading to find out what they really do. Are the ascent a descent arrows specific to pivot tables? Thank you for all of this. You have opened up a lot of possibilities for me to play with.
Your suggestion might work. I will play with it to see how it works. Thanks
Know which cell as been clicked on to retrieve value
