This is a little vague but I'm hoping there's a Google Sheets power user who has some insight.

In game dev, designers prefer to work with spreadsheets. But we often hit situations where we need to describe multi-dimensional data. For example imagine you have crafting items in a crafting game. Each Item can be "salvaged" into other Items. e.g. a Box could be salvaged to make 5 Planks, 2 Rope.

So far our attempts have all had serious flaws:

• a) Make a column for each of the Salvage results. But that would mean you have a variable number of columns for each item. Or you have to have a maximum number of columns. Either way it gets very wide and messy.
• b) Make a row for each of the Salvage results. But this means that rows can now either be Items or Salvage results?
• c) Smoosh the Salvage results into a single column with a custom separator. But this means that formulas don't work, so you can't easily modify values.

Questions:

• Has anyone else in a field outside of game dev dealt with this? Are there other tools out there?
• Is there any way to make tables-within-cells in Google Sheets?
• Is there a way to use Data Validation not only to make dropdowns of single values, but a dropdown with Key-Value pairs?

CodePudding user response：

Has anyone else in a field outside of game dev dealt with this? Are there other tools out there?

For sure! You can easily use built in formulas within Google Sheets to manipulate the data however you may need.

Is there any way to make tables-within-cells in Google Sheets?

Technically, yes. By using two separate delimiters, you can easily turn those strings into functioning arrays.

For example, take a look at this setup:
|Item|Components|Formula|Resulting Array| |---|---|---|---|---| |Enchantment Table|Diamond - 2,Book - 1, Obsidian - 4|=arrayformula(split(transpose(split("Diamond - 2,Book - 1, Obsidian - 4", ",")), " - "))|| ^{(Click the image to enlarge it)}

From here, we can manipulate this data however we want.

You can use the formula above as the array itself. ie, anywhere in a formula that needs an array, you can place the formula there instead (something like A1:B3 could be replaced with the above formula).

You can also use specific items/values from this formula.
If you need to find a number of salvaged items, you can combine this formula with a VLOOKUP():

=VLOOKUP("Diamond", arrayformula(split(transpose(split(VLOOKUP(E2, A2:B, 2), ",")), " - ")), 2)
Which would output a value of 1.

Also, if you only wanted one column of the resulting data, you could use a QUERY:

=query({arrayformula(split(transpose(split(VLOOKUP(E2, A2:B, 2), ",")), " - "))}, "select Col1")

There are several other ways you could use this array, these are just two examples.

Is there a way to use Data Validation not only to make dropdowns of single values, but a dropdown with Key-Value pairs?

Sure, but not without a bit of help.

In order to get a dynamic list of values for a dropdown, you need to include a helper column. This helper column can be anywhere in the spreadsheet, including a different sheet entirely (out of sight, out of mind). For my example though, I made the helper column J.

J1 has the formula =TRANSPOSE(split(VLOOKUP(E2, A2:B4, 2), ",")), which splits the list only into one column. From there, you can set the data validation dropdown to reference this column.

CodePudding user response：

Short Answers:

• Yes
  
• Yes
  
• Yes
  

Take a look

Output table

Paste this formula to get the reaults from the lookup array from lookup table and split it's values.

=ArrayFormula(IF(ArrayFormula(IF(A3:A="",,IFERROR(VLOOKUP(A3:A,J3:P,6,0),"No match")))="",,TRIM(SPLIT(ArrayFormula(IF(A3:A="",,IFERROR(VLOOKUP(A3:A,J3:P,6,0),"No match"))),"-,"))))

Data validation key value pair:

How to create dropdown list