If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 . |
#4
|
|||
|
|||
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 . |
Thread Tools | |
Display Modes | |
|
|