Sum values from arbitrary locations across a worksheet.

[Mr_Kay]

I've been building a calculator for a videogame that I play, in which you design and build large, interconnected automation lines using machines and conveyor belts.
Below is an example of a "Recipe Template" (name WIP). It's meant to calculate and organize various values that are representative of the in-game production lines.
The idea is that the user would be able to copy/paste this template arbitrarily around the worksheet, set different recipes, and connect them together in order to help them design their factories before building them.

The Product/min cell (Q55) is meant to be a sum of the cost/min value from all the other recipes that use that part in the cost.
Right now the user is required to manually reference those cells, and if they change to an alternate recipe for the same product, they must change all associated references.
This can get frustrating late game when you're trying to evaluate the effects of different alternates on the rest of your production lines, which could be almost 100 other recipes.
Is there a way to sum those values without directly referencing the cells, given their arbitrary location and prevalence within the worksheet?

I'm trying to accomplish this without the use of another worksheet or VBA, but 3 days of googling, forum surfing, and trolling through Microsoft Support's function documentation hasn't put me any closer to a solution,
and I'll bend if I have to.

Satisfactory Calculator.xlsx
	L	M	N	O	P	Q	R
53
54		Heavy Encased Frame		Frames	Part	per/min
55				Product:	Heavy Modular Frame	33.75
56		Machine:	Manufacturer	By-product:
57		Amnt. Needed:	12	Cost:	Modular Frame	90
58		Estimate Power (MW):	660		Encased Industrial Beam	112.5
59					Steel Pipe	405
60		Overage:	5.75		Concrete	247.5
61
Recipe Scale Calculator

Cell Formulas
Range	Formula
O54	O54	=IF(M54="", "Category", (IF((INDEX(MasterRecipeList, MATCH(M54, MasterRecipeList[Recipe Name], 0), MATCH("Category", MasterRecipeList[#Headers], 0)))=0, "", (INDEX(MasterRecipeList, MATCH(M54, MasterRecipeList[Recipe Name], 0), MATCH("Category", MasterRecipeList[#Headers], 0))))))
Q55	Q55	=N60+W57+Q239
P55	P55	=IF(M54="", "", (INDEX(MasterRecipeList, MATCH(M54, MasterRecipeList[Recipe Name], 0), MATCH("Product", MasterRecipeList[#Headers], 0))))
P56	P56	=IF(M54="", "", (IF((INDEX(MasterRecipeList, MATCH(M54, MasterRecipeList[Recipe Name], 0), MATCH("By-Product", MasterRecipeList[#Headers], 0)))=0, "", (INDEX(MasterRecipeList, MATCH(M54, MasterRecipeList[Recipe Name], 0), MATCH("By-Product", MasterRecipeList[#Headers], 0))))))
P57	P57	=IF(M54="", "", (IF((INDEX(MasterRecipeList, MATCH(M54, MasterRecipeList[Recipe Name], 0), MATCH("Part 1", MasterRecipeList[#Headers], 0)))=0, "", (INDEX(MasterRecipeList, MATCH(M54, MasterRecipeList[Recipe Name], 0), MATCH("Part 1", MasterRecipeList[#Headers], 0))))))
Q57	Q57	=IF(P57="", "", Q55*((INDEX(MasterRecipeList, MATCH(M54, MasterRecipeList[Recipe Name], 0), MATCH("Part 1 / cycle", MasterRecipeList[#Headers], 0)))/(INDEX(MasterRecipeList, MATCH(M54, MasterRecipeList[Recipe Name], 0), MATCH("Product / cycle", MasterRecipeList[#Headers], 0)))))
P58	P58	=IF(M54="", "", (IF((INDEX(MasterRecipeList, MATCH(M54, MasterRecipeList[Recipe Name], 0), MATCH("Part 2", MasterRecipeList[#Headers], 0)))=0, "", (INDEX(MasterRecipeList, MATCH(M54, MasterRecipeList[Recipe Name], 0), MATCH("Part 2", MasterRecipeList[#Headers], 0))))))
Q58	Q58	=IF(P58="", "", Q55*((INDEX(MasterRecipeList, MATCH(M54, MasterRecipeList[Recipe Name], 0), MATCH("Part 2 / cycle", MasterRecipeList[#Headers], 0)))/(INDEX(MasterRecipeList, MATCH(M54, MasterRecipeList[Recipe Name], 0), MATCH("Product / cycle", MasterRecipeList[#Headers], 0)))))
P59	P59	=IF(M54="", "", (IF((INDEX(MasterRecipeList, MATCH(M54, MasterRecipeList[Recipe Name], 0), MATCH("Part 3", MasterRecipeList[#Headers], 0)))=0, "", (INDEX(MasterRecipeList, MATCH(M54, MasterRecipeList[Recipe Name], 0), MATCH("Part 3", MasterRecipeList[#Headers], 0))))))
Q59	Q59	=IF(P59="", "", Q55*((INDEX(MasterRecipeList, MATCH(M54, MasterRecipeList[Recipe Name], 0), MATCH("Part 3 / cycle", MasterRecipeList[#Headers], 0)))/(INDEX(MasterRecipeList, MATCH(M54, MasterRecipeList[Recipe Name], 0), MATCH("Product / cycle", MasterRecipeList[#Headers], 0)))))
P60	P60	=IF(M54="", "", (IF((INDEX(MasterRecipeList, MATCH(M54, MasterRecipeList[Recipe Name], 0), MATCH("Part 4", MasterRecipeList[#Headers], 0)))=0, "", (INDEX(MasterRecipeList, MATCH(M54, MasterRecipeList[Recipe Name], 0), MATCH("Part 4", MasterRecipeList[#Headers], 0))))))
Q60	Q60	=IF(P60="", "", Q55*((INDEX(MasterRecipeList, MATCH(M54, MasterRecipeList[Recipe Name], 0), MATCH("Part 4 / cycle", MasterRecipeList[#Headers], 0)))/(INDEX(MasterRecipeList, MATCH(M54, MasterRecipeList[Recipe Name], 0), MATCH("Product / cycle", MasterRecipeList[#Headers], 0)))))
N56	N56	=IF(M54="", "", (IF((INDEX(MasterRecipeList, MATCH(M54, MasterRecipeList[Recipe Name], 0), MATCH("Machine", MasterRecipeList[#Headers], 0)))=0, "", (INDEX(MasterRecipeList, MATCH(M54, MasterRecipeList[Recipe Name], 0), MATCH("Machine", MasterRecipeList[#Headers], 0))))))
N57	N57	=IF(M54="", "", Q55/(INDEX(MasterRecipeList, MATCH(M54, MasterRecipeList[Recipe Name], 0), MATCH("Product / min@100%", MasterRecipeList[#Headers], 0))))
N58	N58	=IF(N57="", "", (N57*(INDEX(MasterRecipeList, MATCH(M54, MasterRecipeList[Recipe Name], 0), MATCH("Power(MW) @100%", MasterRecipeList[#Headers], 0)))))

Named Ranges
Name	Refers To	Cells
RecipeNameList	='Master Recipe List'!$D$2:$D$16	Q57:Q60, N56:N58, P55:P60, O54

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O54	Cell Value	contains "Oil"	text	NO
O54	Cell Value	contains "Computers"	text	NO
O54	Cell Value	contains "Motors"	text	NO
O54	Cell Value	contains "Frames"	text	NO
O54	Cell Value	contains "Steel"	text	NO
O54	Cell Value	contains "Native Fauna"	text	NO
O54	Cell Value	contains "Caterium"	text	NO
O54	Cell Value	contains "Quartz"	text	NO
O54	Cell Value	contains "Biofuel"	text	NO
O54	Cell Value	contains "Concrete"	text	NO
O54	Cell Value	contains "Copper"	text	NO
O54	Cell Value	contains "Iron"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
P55	Any value
M54:N54	List	='Master Recipe List'!$D$2:$D$236

 

[Sam_D_Ben]

Right now the user is required to manually reference those cells, and if they change to an alternate recipe for the same product, they must change all associated references.

Could you explain this section in more detail?

I'm trying to accomplish this without the use of another worksheet or VBA

Let’s take a look

 

[Mr_Kay]

Could you explain this section in more detail?

Let’s take a look

Sure thing.

The game offers different ways to make the same part (different cost parts, different machine, ect.), these are referred to as alternate recipes.
When the user changes from one recipe to another, the template automatically pulls the relevant cost parts and values from a table in another worksheet.
It also recalculates all of the per/min values based on the new recipe. However, the Product/min value is (currently) a hard coded sum of references (=C3+F55+AB223...), and does not change when the user selects a new recipe.
Example: The above recipe for Heavy Modular Frames shows the last part is Concrete. If the user switches the recipe to an alternate for Heave Modular Frames that does not require concrete, the concrete template will still be referencing that cell, and thus the sum value for concrete will be wrong. The user then has to manually remove that cell reference to make the number accurate again.
The goal is to automate the process of summing these values, without knowing exactly how many there will be or where they are.
The part name will always be to the left of the value, so my initial thought was to search for all instances of that text string, then sum the value to the right of those cells, but I was unsuccessful.

 

[Sam_D_Ben]

Could you share somemore screenshots of your working file related to this? {Image speaks louder }

---AND
"MasterRecipeList" & 'Master Recipe List'!$D$2:$D$236, seems you have named table and sheet with the same name.

 

[Mr_Kay]

This is where each template pulls it's data from.

This is a section of the calculator with 'Trace Precedents' on a few cells.

Hope this helps.

 

[Sam_D_Ben]

Thank you, I appreciate it

 

[Sam_D_Ben]

I understand your SUM requirement completely. From what I observe, data in the Recipe Scale Calculator is populated solely based on the Master Recipe List. AND Each templates appears to be separate. However the question is: how does the user switch between alternate recipes? For example, in cell M54, is the user selecting a different recipe name to switch recipes, or is there another mechanism to switch. Is switching means selecting a different recipe name ? Confused

Additionally, for the stator in O65, I notice that your lines are pointing to two stators W68 and AC70. I'm unsure about the logic behind this connection. Could you clarify these two points?

 

[Mr_Kay]

how does the user switch between alternate recipes?

The cell in the upper left corner of the template is a data validation drop list, that pulls from the Recipe Name column of the MasterRecipeList. The other cells then use that recipe name in an index/match function to pull the other text or values needed. The Cost/min cells will each pull 2 numbers and divide them to get a ratio, then multiply that ratio by Product/min to get final cost for that item at that rate.

Additionally, for the stator in O65, I notice that your lines are pointing to two stators W68 and AC70

As the game progresses, each new item you need to automate requires the previous tier of items to make. If I need to make more Motors, that means I need more Stators. More stators means more Steel Pipes, more Pipes means more Ingots, ect. You also gradually unlock new alternate recipes, some of which might be better suited for the kind of factory you're building.
Some items are required for multiple downstream production lines, and the Product/min value is a sum of those demands, plus the Overage (which is a user defined surplus for that item). The ideal scenario is that when the user selects a new alternate recipe, the Product/min should stay the same, and the cost updates to the new items and rates. That change then propagates backward through the factory, recalculating each item to it's new demand rate.

Another larger section of my factory with more 'Trace Precedents'.

Hope this helps :D

 

[Sam_D_Ben]

the Product/min should stay the same

Have you tested this possible approach? Once Product/min calculates a value, and the formula is replaced with a static rusult automatically in that cell. Future changes to the template won’t update the result. To modify it, you need to clear the cell content and then re-enter the new formula; otherwise, the Product/min value remains unchanged.

 

[Sam_D_Ben]

Honestly, I don’t think there’s an Excel formula that can really handle this situation. I tried using INDIRECT and ADDRESS, but you just end up stuck in that annoying circular reference trap. The only way around it seems to be VBA.

Also, just a note — since your sample data is shared as images, it’s tough for people to help out properly. Most folks need actual or dummy data to test things their own way. I am sure someone will crack it using Excel formula.

From what I see, VBA looks like the best (maybe the only) way to go here. Still, even with VBA, circular references can be tricky to deal with.
That said, I do think there’s a solution for what you’re trying to do — Also I’m just not fully clear on the game logic yet. Once I get that part, I think it’ll click.
I tried UDF and it seems working, Just see the simple demo for now, but it shows the idea, can develop it.

In this demo, the UDF handles it so that when a user switches to an alternate recipe, the Product/min doesn't end up calculating the wrong value. Plus, if needed, you can even make it static.

Sum values from arbitrary locations across a worksheet_Mr_Kay.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		Wire		Copper	Constructor	per/min		Cable		Copper	Constructor	per/min
3				Product:	Wire	2				Product:	Cable	10
4		Machine:	Constructor	By-product:				Machine:	Constructor	By-product:
5		Amnt. Needed:	30	Cost:	Copper Ingot	1		Amnt. Needed:	30	Cost:	Wire	2
6		Estimate Power (MW):	4					Estimate Power (MW):	4
7
8		Overage:	1					Overage:	2
9
10
11		Rubber Concrete		Concrete	Assembler	per/min		Biomass (Leaves)		Biofuel	Constructor	per/min
12				Product:	Concrete	10				Product:	Biomass	2
13		Machine:	Assembler	By-product:				Machine:	Constructor	By-product:
14		Amnt. Needed:	90	Cost:	Limestone	10		Amnt. Needed:	60	Cost:	Leaves	10
15		Estimate Power (MW):	15		Rubber	2		Estimate Power (MW):	4
16
17		Overage:	3					Overage:	4
18
Recipe Scale Calculator

Cell Formulas
Range	Formula
F3	F3	=SumRightOfKeywords(B2:L17,"Wire")
L3	L3	=SumRightOfKeywords(B4:L17,"LimeStone")
F12	F12	=SumRightOfKeywords(B4:L17,"Leaves")
L12	L12	=SumRightOfKeywords(B13:L17,"Rubber")