Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Function or Formula in Excel
Hi
new to this. in cell B13 I have a drop down box with 3 options Post Courier Air freight and I need function that will allow me to have a different answer when each is selected the out put in B12 eg when Post is selected I wish the price which is located in say D4 to come up in B12 when Courier is selected the price to come which is located in E4to come up in B12 and the same with Air freight Would be most appreciated if some could help me with this Rod |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Function or Formula in Excel
Label in D3:F3 the 3 DV choices: Post, Courier, Air freight
Then place in B12: =IF(B13="","",HLOOKUP(B13,D3:F4,2,0)) -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:363 Subscribers:64 xdemechanik --- "Rod" wrote: in cell B13 I have a drop down box with 3 options Post Courier Air freight and I need function that will allow me to have a different answer when each is selected the out put in B12 eg when Post is selected I wish the price which is located in say D4 to come up in B12 when Courier is selected the price to come which is located in E4 to come up in B12 and the same with Air freight |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Function or Formula in Excel
Hi Rod,
According to the value of cells you have given, here is a formula that may go in B12: =IF($B$13="Post",$D$4,IF($B$13="Courier",$E$4,IF($ B$13="Air Freight",$F $4))) whe B13 as you mentioned is the drop down list, D4 = price for Post E4 = price for Courier F4 = price for Air Freight In case you expand these lists, you can still expand the values as well, for a fourth price say and a fourth company. If this helps, please leave a feedback Regards, Totti |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Function or Formula in Excel
Many thanks Max
Rod "Max" wrote: Label in D3:F3 the 3 DV choices: Post, Courier, Air freight Then place in B12: =IF(B13="","",HLOOKUP(B13,D3:F4,2,0)) -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:363 Subscribers:64 xdemechanik --- "Rod" wrote: in cell B13 I have a drop down box with 3 options Post Courier Air freight and I need function that will allow me to have a different answer when each is selected the out put in B12 eg when Post is selected I wish the price which is located in say D4 to come up in B12 when Courier is selected the price to come which is located in E4 to come up in B12 and the same with Air freight |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Function or Formula in Excel
Many Thanks Totti
perhaps you could also tell me please how to write a function to give me the lowest amount of freight from the three options used Much appreciated Rod "Totti" wrote: Hi Rod, According to the value of cells you have given, here is a formula that may go in B12: =IF($B$13="Post",$D$4,IF($B$13="Courier",$E$4,IF($ B$13="Air Freight",$F $4))) whe B13 as you mentioned is the drop down list, D4 = price for Post E4 = price for Courier F4 = price for Air Freight In case you expand these lists, you can still expand the values as well, for a fourth price say and a fourth company. If this helps, please leave a feedback Regards, Totti |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Function or Formula in Excel
to get the lowest amount, i think it would be proper to use the
function MIN, like if you have your amounts concerning FREIGHT, in the cells B1,C1,D1,E1, =MIN(B1:E1) apply MIN to the range of prices and it would give you the smallest Regards, Totti |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Function or Formula in Excel
Welcome, Rod
-- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "Rod" wrote in message ... Many thanks Max Rod |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Function or Formula in Excel
Hi Max
I have a similar query, and was hopeing you maybe able to help based on the above? I'm building a reporting template on one tab I hold all the DV List values Customer, Sector, etc etc The next tab is the actual report, in IT Open Issues Log!D4 They will define the Sector, the values of which are on =Backend!$H$2:$H$7 when they are adding line items they will select the Customer affected they do this in IT Open Issues Log!B7 for the first item and the Values come from =Backend!$A$2:$A$131 The Next Item would be in B8, B9, B10 etc etc What I would like to do is to have IT Open Issues Log!B* DV Lists filtered by Sector, the Customer list has a Sector listed against it in =Backend!$B$2:$B$131 I hope that makes sense?? If they would let me create a database i'd be happier, but they'd die if they couldn't use Office Products to do management reporting "Max" wrote: Label in D3:F3 the 3 DV choices: Post, Courier, Air freight Then place in B12: =IF(B13="","",HLOOKUP(B13,D3:F4,2,0)) -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:363 Subscribers:64 xdemechanik --- "Rod" wrote: in cell B13 I have a drop down box with 3 options Post Courier Air freight and I need function that will allow me to have a different answer when each is selected the out put in B12 eg when Post is selected I wish the price which is located in say D4 to come up in B12 when Courier is selected the price to come which is located in E4 to come up in B12 and the same with Air freight |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Function or Formula in Excel
This thread is long closed.
Suggest that your start a new thread for your query. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EXCEL FUNCTION OR FORMULA | Excel Discussion (Misc queries) | |||
Excel formula / IF function | Excel Worksheet Functions | |||
i am in search of Formula / Function in EXCEL-2003 | Excel Discussion (Misc queries) | |||
excel formula/function woes | Excel Worksheet Functions | |||
How do I create a multi formula IF function in Excel? | Excel Worksheet Functions |