Data validation and INDIRECT to pull only selected data from a single worksheet onto another

[Rob Atron]

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"):

[TABLE="class: grid, width: 326"]
<tbody>[TR]
[TD]Ingredients[/TD]
[TD]type[/TD]
[TD]Healing[/TD]
[TD]Sedative[/TD]
[/TR]
[TR]
[TD]Fireweed Seed[/TD]
[TD]pos[/TD]
[TD]80[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]neut[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]neg[/TD]
[TD]
[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: grid, width: 326"]
<tbody>[TR]
[TD]Ingredients[/TD]
[TD]type[/TD]
[TD]Healing[/TD]
[TD]Sedative[/TD]
[/TR]
[TR]
[TD]Fireweed Seed[/TD]
[TD]pos[/TD]
[TD]80[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]neut[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]neg[/TD]
[TD]
[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]None[/TD]
[TD]pos[/TD]
[TD="align: right"]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]neut[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]neg[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]None[/TD]
[TD]pos[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]neut[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]neg[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

 

[Snakehips]

Rob,

Welcome to MrExcel.

Here is some food for thought, maybe.

The dropdown list is populated from Ingredients sheet column A by defining the range, naming the range or dynamic range. Whatever suits.

Then you could retain the current format of your ingredients by listing in columns eg C:F.

On that basis, the formula in B2 wil copy across and down to get your selected ingredient properties.

Excel 2007
	A	B	C	D	E	F
1	Ingredients		Ingredient	type	healing	sedative
2	None		None
3	FireWeed Seed
4	Bird Seed
5	Linseed		FireWeed Seed	pos	80
6	Aniseed			neut
7				neg		10
8			Bird Seed	pos	20
9				neut
10				neg		30
11			Linseed	pos	2
12				neut
13				neg		98
14			Aniseed	pos	70
15				neut
16				neg		10
Ingredients

Excel 2007
	A	B	C	D
1	Ingredients	type	Healing	Sedative
2	FireWeed Seed	pos	80
3		neut
4		neg		10
5	Linseed	pos	2
6		neut
7		neg		98
8	None
9
10
Master

Cell Formulas
Range	Formula
B2	=OFFSET(Ingredients!$C$1,MATCH(INDIRECT("$A"&(COUNTA($A$2:$A2)*3)-1),Ingredients!$C:$C,0)+(MOD(ROWS($A$2:$A4),3)-1),COLUMNS($B:B))

Named Ranges
Name	Refers To	Cells
Ingredients	=Ingredients!$A$2:$A$6

It would be similar but probably be more simple to have the ingredients data in single rows.

Hope that helps.

 

[Rob Atron]

Snakehips,
I have used a variant of your formula and it's working great. Thanks a lot!

Rob