Lookup and Add

[tykc17]

I have a list of codes on my Information tab and a corresponding Type. On my Supplies tab I want total the cost by looking up the code type from Information tab and then calculate the cost of those codes in the Supplies tab. I'm not sure if this is a feasible way of doing this or if I just need to has the US or MNT in the Supplies tab to make it easier. The point of the Information tab is to list information that doesn't need to be adjusted by the user and they just need to select it from the Supplies tab

I can't use the XL2BB on my work computer so I'm doing my best with what I got.

Information Tab

Code	Type
GFE1	US
GFE2	MNT
GFE3	MNT
GFE4	US
GFE5	US

Supplies Tab

US = 450

MNT = 200

Code	Cost	QTY	Total
GFE1 (US)	50	5	250
GFE1 (US)	25	5	125
GFE4 (US)	15	5	75
GFE2 (MNT)	10	5	50
GFE3 (MNT)	30	5	150

 

[Ezguy4u]

tykc let's get the ball rolling. Here is my solution to your problem. Now this is just one solution. There may be some A+ students that weigh in and give us an even better solution. All this sheet is a formula to start. Once you say it works than we can move on from there. If you have any questions, comments or suggestions let us know.

24-09-09 lookup add work 1.xlsx
	A	B	C	D	E	F	G	H
1	US =	450		MNT =	200			GFE1 (US)
2								GFE4 (US)
3								GFE5 (US)
4								GFE2 (MNT)
5								GFE3 (MNT)
Supplies Tab

Cell Formulas
Range	Formula
B1	B1	=SUM(SUMIFS('Information Tab'!D2:D6,'Information Tab'!A2:A6,'Supplies Tab'!H1:H3))
E1	E1	=SUM(SUMIFS('Information Tab'!D2:D6,'Information Tab'!A2:A6,'Supplies Tab'!H4:H5))

24-09-09 lookup add work 1.xlsx
	A	B	C	D
1	Code	Cost	QTY	Total
2	GFE1 (US)	50	5	250
3	GFE1 (US)	25	5	125
4	GFE4 (US)	15	5	75
5	GFE2 (MNT)	10	5	50
6	GFE3 (MNT)	30	5	150
Information Tab