Calcs & Data Validation

[Andrew]

Looking for an easy way to complet the following. I imagine their must be a
quick & easy method.
Cell A5 has a drop down list which corresponds to a range on another
worksheet. It has 300+ entries. In cell B5 i will enter a number/qty. What i
would like is that as each of the items in the list has a corresponding cost
(on other worksheet), i would like to know an easy way to calculate the cost
(in C5) based upon the qty entered (B5) & the cost of the item selected.
I have done it before with 'IF' function for a small selection, but figure
there must be an easier way with 300+ items.
Hope i havent made this to confusing

Thanks in advance for any assistance

 

[T. Valko]

See if this is what you had in mind...

Assuming each item in the list is unique.

Sheet2 A2:A300 = items
Sheet2 B2:B300 = price

Sheet1 A5 = drop down list of items
Sheet1 B5 = qty
Sheet1 C5 = formula:

=SUMIF(Sheet2!A$2:A$300,A5,Sheet2!B$2:B$300)*B5

 

[Andrew]

Genius Biff. works great
Thanks mate.

See if this is what you had in mind...

Assuming each item in the list is unique.

Sheet2 A2:A300 = items
Sheet2 B2:B300 = price

Sheet1 A5 = drop down list of items
Sheet1 B5 = qty
Sheet1 C5 = formula:

=SUMIF(Sheet2!A$2:A$300,A5,Sheet2!B$2:B$300)*B5

--
Biff
Microsoft Excel MVP





.

 

[T. Valko]

You're welcome. Thanks for the feedback!