Hi there,
Background
I'm an indie game developer working on a spreadsheet (Excel 2007) which defines how different ingredients can be mixed in various ways to create different potions.
Source worksheets
Data for each ingredient is included in a separate source worksheet, that I'll call ingredient worksheets. Within these ingredient worksheets, each column defines a different property for the ingredient (e.g. healing, sedative, etc.), with the potency of every property in numerical form in that column. As shown below, these use a slightly unusual layout, because each property has three possible ways of acting: positively (e.g. healing), negatively (e.g. poison), or neutralising (e.g. antidote). At the moment these three property types are included in separate rows, labelled: "pos", "neg" and "neut".
I've simplified the spreadsheets considerably, but the ingredient worksheet looks something like this (e.g. for the ingredient: "Fireweed Seed"):
<tbody>
</tbody>
Master worksheet
Apart from the ingredient worksheets, I also have one master worksheet, which is basically a sandbox for testing out different ingredient combinations. As it is at the moment, I can select multiple ingredients to add to this ‘cauldron’ using data validation dropdown menus. I then use INDIRECT to reference the relevant ingredient worksheets and pull the data from those required ingredient worksheets into the master worksheet. The master worksheet then calculates the properties of the resulting potion (not shown below).
The master worksheet (without any calculations) looks something like this:
<tbody>
</tbody>
NOTE: the names in blue in the Ingredients column above are selectable using dropdown menus. So if - as above - I set one dropdown as "Fireweed Seed" then it pulls the data from the "Fireweed Seed" ingredients workbook. "None" references a blank worksheet.
The problem
This all works very nicely. The only problem with this system is that we plan to add many more ingredients in the future, and this will mean having a lot of worksheets.
So, I would like to know how it might be possible to instead have all the ingredient data inside a single worksheet, but still allow the master worksheet to pull only data regarding the ingredients selected using the dropdown menus.
NOTE: the three-row (pos/neut/neg) layout is ideal for the master worksheet because of the calculations involved, and it was only used in the ingredient worksheets to keep the layout and cell references the same. If possible I would like to keep it that way, but if that just complicates things then I am prepared to change it so that all data for one ingredient is included on one row.
Many thanks
Rob
Background
I'm an indie game developer working on a spreadsheet (Excel 2007) which defines how different ingredients can be mixed in various ways to create different potions.
Source worksheets
Data for each ingredient is included in a separate source worksheet, that I'll call ingredient worksheets. Within these ingredient worksheets, each column defines a different property for the ingredient (e.g. healing, sedative, etc.), with the potency of every property in numerical form in that column. As shown below, these use a slightly unusual layout, because each property has three possible ways of acting: positively (e.g. healing), negatively (e.g. poison), or neutralising (e.g. antidote). At the moment these three property types are included in separate rows, labelled: "pos", "neg" and "neut".
I've simplified the spreadsheets considerably, but the ingredient worksheet looks something like this (e.g. for the ingredient: "Fireweed Seed"):
| Ingredients | type | Healing | Sedative |
| Fireweed Seed | pos | 80 | |
| neut | |||
| neg | 10 |
<tbody>
</tbody>
Master worksheet
Apart from the ingredient worksheets, I also have one master worksheet, which is basically a sandbox for testing out different ingredient combinations. As it is at the moment, I can select multiple ingredients to add to this ‘cauldron’ using data validation dropdown menus. I then use INDIRECT to reference the relevant ingredient worksheets and pull the data from those required ingredient worksheets into the master worksheet. The master worksheet then calculates the properties of the resulting potion (not shown below).
The master worksheet (without any calculations) looks something like this:
| Ingredients | type | Healing | Sedative |
| Fireweed Seed | pos | 80 | |
| neut | |||
| neg | 10 | ||
| None | pos | ||
| neut | |||
| neg | |||
| None | pos | ||
| neut | |||
| neg |
<tbody>
</tbody>
NOTE: the names in blue in the Ingredients column above are selectable using dropdown menus. So if - as above - I set one dropdown as "Fireweed Seed" then it pulls the data from the "Fireweed Seed" ingredients workbook. "None" references a blank worksheet.
The problem
This all works very nicely. The only problem with this system is that we plan to add many more ingredients in the future, and this will mean having a lot of worksheets.
So, I would like to know how it might be possible to instead have all the ingredient data inside a single worksheet, but still allow the master worksheet to pull only data regarding the ingredients selected using the dropdown menus.
NOTE: the three-row (pos/neut/neg) layout is ideal for the master worksheet because of the calculations involved, and it was only used in the ingredient worksheets to keep the layout and cell references the same. If possible I would like to keep it that way, but if that just complicates things then I am prepared to change it so that all data for one ingredient is included on one row.
Many thanks
Rob
