Costings using VLOOKUP based on multiple dropdown options

[GemmaTH]

Hi all,

New to the forum and my Excel skills need a bit of work! Hoping someone might be able to help please?

I am currently creating a costing spreadsheet - each tab/worksheet relates to a specific supplier and contains the items they sell and their prices. I'd like to create a front sheet using drop down lists & formulas that will pull costing information through into a quote.

For example, cell D9 contains a dropdown list created using data validation and a source list named "Supplier". Cell E9 then populates with a further drop down showing the list of items available from this supplier, using data validation "source =indirect(D9)". Cell F9 contains the quantity needed and, in cell G9, I want to calculate the cost of the purchase.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Supplier[/TD]
[TD]Item[/TD]
[TD]Quantity[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]ABC[/TD]
[TD]Timber[/TD]
[TD]2[/TD]
[TD]£[/TD]
[/TR]
</tbody>[/TABLE]


I have tried using VLOOKUP to do this but can't work out how to get the table array to change depending on what's been chosen in Column D?

For example, for supplier ABC, I have used VLOOKUP(E9,ABC!$C$10:$D$52,2)*F9. This works well and will calculate exactly what I need (the items being in column C and costs being in column D of the ABC tab). However, this formula still points to supplier ABC's price list, even if I select supplier DEF from the drop down in column D.

Is there a way for the VLOOKUP formula to change in accordance with which supplier has been selected in D9? Or should I be using a different formula / method entirely?

I hope this makes sense - as I said, I'm still learning when it comes to anything more than a very basic spreadsheet! It would be great if somebody can help shed some light.

Thanks,
Gemma

 

[AlanY]

have a look of this

https://excelchamps.com/blog/dynamic-drop-down-list/

 

[GemmaTH]

Hi Alan,

Thanks so much for your quick reply. I think I'm ok with creating the drop down lists themselves, what I'm having trouble with is the formula to work out costs based on what has been selected.

Sorry, I may not have been very clear with my question in my original post! Essentially,what I need to know is how to get the VLookup (or perhaps sum?) formula to automatically adjust depending on what has been selected in the drop downs.

e.g. If I select Supplier "ABC" and item "timber" I need the formula in column G to find the unit price of the timber and then multiply it by the specified quantity in column F to calculate the cost. Similarly, if in the row below, I choose Supplier "DEF", the formula will then need to know to point to DEF's underlying worksheet/tab within the spreadsheet to find the correct price.

Is this something that you might be able to help with?

Thanks again!
G

 

[AlanY]

it will be easier if you can arrange your item/cost in a table like this

Book1
	A	B	C	D	E	F	G	H
1	Supplier	Item	Cost		Supplier	Item	Quantity	Cost
2	ABC1	Item1	10		ABC2	Item8	2	160
3	ABC1	Item2	20
4	ABC1	Item3	30
5	ABC1	Item4	40
6	ABC1	Item5	50
7	ABC2	Item6	60
8	ABC2	Item7	70
9	ABC2	Item8	80
10	ABC2	Item9	90
11	ABC2	Item10	100
12	ABC3	Item11	110
13	ABC3	Item12	120
14	ABC3	Item13	130
15	ABC3	Item14	140
16	ABC3	Item15	150
17	ABC4	Item16	160
18	ABC4	Item17	170
19	ABC4	Item18	180
20	ABC4	Item19	190
21	ABC4	Item20	200
Sheet7

Cell Formulas
Range	Formula
H2	=G2*SUMIFS($C$2:$C$21,$A$2:$A$21,E2,$B$2:$B$21,F2)

 

[GemmaTH]

Yes, that does look much easier (thank you!), but I've been asked to keep a supplier per tab for formatting by my colleagues who will eventually use this. I'm looking at more than 30 suppliers, and a whole range of stock items for each, so the idea is that we keep a price list for each supplier and a "master" sheet that will pull the required information through into a quote.

Really appreciate your help - I'm not even sure if what I am trying to do is possible!

 

[AlanY]

may be you can try this

suppliers sheets

Book1
	A	B	C	D
1			Item1	110
2			Item2	120
3			Item3	130
4			Item4	140
5			Item5	150
6			Item6	160
7			Item7	170
8			Item8	180
9			Item9	190
10			Item10	200
Supplier1

Book1
	A	B	C	D
1			Item1	210
2			Item2	220
3			Item3	230
4			Item4	240
5			Item5	250
6			Item6	260
7			Item7	270
8			Item8	280
9			Item9	290
10			Item10	300
Supplier2

Book1
	A	B	C	D
1			Item1	310
2			Item2	320
3			Item3	330
4			Item4	340
5			Item5	350
6			Item6	360
7			Item7	370
8			Item8	380
9			Item9	390
10			Item10	400
Supplier3

and the master front sheet

Book1
	A	B	C	D
1	Supplier	Item	Quantity	Cost
2	Supplier1	Item6	1	160
3	Supplier2	Item7	1	270
4	Supplier3	Item8	1	380
5	Supplier2	Item9	1	290
6	Supplier1	Item10	1	200
Master

Cell Formulas
Range	Formula
D2	=C2*SUMIF(INDIRECT(A2&"!c:c"),B2,INDIRECT(A2&"!d:d"))

 

[GemmaTH]

You are a genius.
That works perfectly, thank you so much, really appreciate your help!

 

[AlanY]

you're welcome

 

[AlanY]

btw, to answer your original question you can use vlookup instead

=C2*VLOOKUP(B2,INDIRECT(A2&"!C:D"),2,0)