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.
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 |
