Select data from table using data from dynamic drop down list

[vampsthevampyre]

Can anybody help, been trying to find a solution to this problem (google wasn't my friend and gave me a load of junk to read!!).

I have three tables,

1. a list of conductors, conductor sizes and costs from several suppliers (Conductors/prices)
2. a list of allowable conductors and sizes for a particular product (Allowable Designs)
3. a product creation screen that allows various scenarios to be tried.(Current Design)

I have set up the "conductors/prices" and "allowable designs" tables to have dynamic names so I can add more products and designs when needed.
Using the dynamic names from the "allowable design" table a have set up validated lists so the user can only select from certain designs.

What I'm having trouble with is getting the current design table with its selected design to reference the "conductors/prices" table and return a corresponding price.

examples of the tables are below:-
Conductors/Prices

[TABLE="width: 500"]
<tbody>[TR]
[TD]Conductors[/TD]
[TD]Diameter[/TD]
[TD]Area[/TD]
[TD]Design[/TD]
[TD]Supplier1[/TD]
[TD]Supplier2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1.36[/TD]
[TD]1.48[/TD]
[TD]1.50[/TD]
[TD]0.90[/TD]
[TD]1.00[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1.37[/TD]
[TD]1.49[/TD]
[TD]1.50[/TD]
[TD]1.00[/TD]
[TD]1.10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1.38[/TD]
[TD]1.50[/TD]
[TD]1.50[/TD]
[TD]1.10[/TD]
[TD]1.20[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1.78[/TD]
[TD]2.50[/TD]
[TD]2.50[/TD]
[TD]1.30[/TD]
[TD]1.40[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]0.53[/TD]
[TD]1.50[/TD]
[TD]1.50[/TD]
[TD]1.40[/TD]
[TD]1.30[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]0.67[/TD]
[TD]2.50[/TD]
[TD]2.50[/TD]
[TD]1.50[/TD]
[TD]1.40[/TD]
[/TR]
</tbody>[/TABLE]


Allowable Designs
[TABLE="width: 500"]
<tbody>[TR]
[TD]Design[/TD]
[TD]Conductors[/TD]
[TD]Diameter[/TD]
[/TR]
[TR]
[TD]1.50[/TD]
[TD]1[/TD]
[TD]1.37[/TD]
[/TR]
[TR]
[TD]1.50[/TD]
[TD]1[/TD]
[TD]1.38[/TD]
[/TR]
[TR]
[TD]2.50[/TD]
[TD]1[/TD]
[TD]1.78[/TD]
[/TR]
[TR]
[TD]1.50[/TD]
[TD]7[/TD]
[TD]0.53[/TD]
[/TR]
[TR]
[TD]2.50[/TD]
[TD]7[/TD]
[TD]0.67[/TD]
[/TR]
</tbody>[/TABLE]



The user would then have several validated dropdown boxes in each design to select number of conductors, Diameter and supplier and the table would look up the cost

Design 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]Design[/TD]
[TD]Conductors[/TD]
[TD]Diameter[/TD]
[TD]Supplier[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]1.50[/TD]
[TD]1[/TD]
[TD]1.37[/TD]
[TD]Supplier1[/TD]
[TD]1.00[/TD]
[/TR]
</tbody>[/TABLE]


Design 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]Design[/TD]
[TD]Conductors[/TD]
[TD]Diameter[/TD]
[TD]Supplier[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]2.50[/TD]
[TD]7[/TD]
[TD]0.67[/TD]
[TD]Supplier2[/TD]
[TD]1.40[/TD]
[/TR]
</tbody>[/TABLE]

I have tried using the Index(match) but a cannot get it to look up an array from a validated drop down list of named dynamic ranges.

Any help would be appreciated

Regards

Ian.

 

[L. Howard]

Hi Ian.

I think I got it figured out, couple questions??

You show a Design 1 and a Design 2, now are those 2 examples from your drop down cells, or do you actually have two sets of drop downs?
I'm taking it as one set of drop downs, showing a couple of examples, but again only from one set of drop downs.

And on your Allowable Designs I presume those are the only combinations that are allowed to be used, that is, there cannot be a [1.50 - 7 - 1.38] design.

And are you allowed to use a helper column and one helper cell?

Howard

 

[Peter_SSs]

Welcome to the MrExcel board!

I have set up the "conductors/prices" and "allowable designs" tables to have dynamic names ..

Rather than the tables having "dynamic names", I assume that you mean that you have set up names that have "dynamic ranges", commonly referred to as "dynamic named ranges". I also assume that the "Allowable Designs" section is only used to enable the various drop-downs available to the user, not in the final calculation that you are asking about.

In my screen shot below, instead of a dynamic named range, I have set up A3:F9 as an Excel 'Table' (via the Insert ribbon tab). That will work like your dynamic named range in that it will automatically expand if new rows are added.
I have then gone into the Name Manger (Formulas ribbon tab) and edited the table name from "Table1" to "CP".
Formula in E22 is copied to other "cost calculation cells" (eg E26)

Excel Workbook
	A	B	C	D	E	F
2
3	Conductors	Diameter	Area	Design	Supplier1	Supplier2
4	1	1.36	1.48	1.5	0.9	1
5	1	1.37	1.49	1.5	1	1.1
6	1	1.38	1.5	1.5	1.1	1.2
7	1	1.78	2.5	2.5	1.3	1.4
8	7	0.53	1.5	1.5	1.4	1.3
9	7	0.67	2.5	2.5	1.5	1.4
10
19
20	Design 1
21	Design	Conductors	Diameter	Supplier	Cost
22	1.5	1	1.37	Supplier1	1
23
24	Design 2
25	Design	Conductors	Diameter	Supplier	Cost
26	2.5	7	0.67	Supplier2	1.4
Design Cost

 

[vampsthevampyre]

Thank you @Peter_SSs. This solution works perfectly, the #Ref was doing my head in. Never used the table function before, but it now looks like it could be my new favourite function in excel for this week.

Once again thanks for the prompt reply and after re-reading my post I'm amazed that you worked out correctly what I was trying to do as my description was severely lacking.

Cheers

Ian

 

[Peter_SSs]

You are welcome. Glad it worked out well for you.