I’m creating a spreadsheet to track recipe combinations for a cooking game I play. I need help with maintaining marked items when my list updates automatically.
My current formula looks like this:
=TOCOL(MAKEARRAY(COUNTA(B3:B)*COUNTA(D3:D)*COUNTA(F3:F), 1, LAMBDA(row, col, INDEX(FILTER(B3:B, B3:B<>""), 1+MOD(INT((row-1)/COUNTA(D3:D)/COUNTA(F3:F)), COUNTA(B3:B))) & " plus " & INDEX(FILTER(D3:D, D3:D<>""), 1+MOD(INT((row-1)/COUNTA(F3:F)), COUNTA(D3:D))) & " using " & INDEX(FILTER(F3:F, F3:F<>""), 1+MOD(row-1, COUNTA(F3:F))) )))
This generates a complete list of all possible combinations from three ingredient columns.
The problem happens when I add new ingredients to my source data. The entire list regenerates and shifts around, but any cells I previously highlighted or marked stay in their old positions. This means my markings end up on completely different combinations than intended.
I tried adding checkboxes to track completed items, but editing the generated cells causes formula errors. Is there a way to either append new combinations to the bottom instead of regenerating everything, or create a system that remembers which specific combinations were marked regardless of their position in the list?
I hit the same issue building a dynamic inventory tracker. Google Sheets treats your generated list as volatile data that rebuilds from scratch every time - that’s your problem. Here’s what worked for me: create a separate tracking table using VLOOKUP or XLOOKUP to match your combinations with a status column. Set up a helper sheet with two columns - combination text and completion status. Then use =IFERROR(VLOOKUP(A2,TrackingSheet!A:B,2,FALSE),FALSE) to pull status into your main list. New combinations show as unmarked while existing tracked items keep their status even when positions change. You’ll manually add new combinations to the tracking sheet, but this completely fixes the shifting problem. Downside is maintaining two sheets, but it’s way more reliable than trying to make the dynamic list preserve formatting.
Your formula looks great, but dynamic arrays always have this regeneration issue. I ran into the same thing tracking item crafting in an RPG spreadsheet. Don’t fight the formula behavior - switch to a query-based approach instead. You’ll have way better control over sort order. Wrap your combination generator in a QUERY function and sort by the combination text itself. This keeps everything alphabetical no matter when you add ingredients. For marking stuff as done, use a separate column with checkboxes that reference the combination text, not the cell position. Try =COUNTIF(CompletedList,A2)>0 to auto-check items that exist in your completed combinations range. When you mark something done, just copy the combination text to your completed list. The checkbox state follows the actual combination content instead of the cell location. Takes more setup but completely fixes the shifting problem.
pivot table’s your best bet here. set up ingredients as source data, then build combinations on a separate sheet with status tracking. way cleaner than messy formulas and won’t break when you add new stuff. i’ve used this approach for game progression tracking - works like a charm.