Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation Data using Validation Table cell range..... | Excel Discussion (Misc queries) | |||
getting pivot to show finctions data calcs across columns, not down rows | Excel Worksheet Functions | |||
data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
Data validation with validation lists and combo boxs | Excel Discussion (Misc queries) |