Calcs & Data Validation
 

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

Calcs & Data Validation
 

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


"Andrew" wrote in message
...
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

Calcs & Data Validation
 

Genius Biff. works great
Thanks mate.

"T. Valko" wrote:

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


"Andrew" wrote in message
...
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


.

Calcs & Data Validation
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Andrew" wrote in message
...
Genius Biff. works great
Thanks mate.

"T. Valko" wrote:

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


"Andrew" wrote in message
...
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


.