# Solved Excel IF Statement & Data Validation

February 14, 2013 at 06:52:29
Specs: Windows XP
 I have a drop down list (A14) with different ports and depending on which port is selected, cell C16 gives you the corresponding value. The problem I face is that, in cell D16, there are varying "minimum" calculations that need to be arrived at depending on the port of choice as there are different rates for each port. Is there an IF function, Choose function, Data Validation or any combnation that I can use complete the calculations in D16?

See More: Excel IF Statement & Data Validation

#1
February 14, 2013 at 10:01:32

Report •

#2
February 14, 2013 at 11:52:49
 Ok. Let me put it this way. I am trying to to a estimate type worksheet, where different airlines have quoted me thier rates to different port all over the world. Now these different airlines have different minimum charges allowed eg. if something costs 0.35 cents per pound, and your freight weighs 50 pounds, that works out to actually \$17.50, but their minimum charge is \$87.50.So my formula for that would be:=IF(A19<250,87.50,A19*C16)Now the issue is that each port of call has a different cost per pound and a different minimum charge.I want to be able to have each formula that I have created (on a separate "sheet4")for each airline, to be linked to each choice that is made for each airline in cell A16.I have 26 different formulas to link to cell D16 based on which destination port is selected in cell A16. Hope this makes it clearer till I can figure out how to attach a copy of the worksheet.I am unable to get the sheet to upload. Dunno why. Prolly doing something wrong.I have 26 different formulas for the 26 different ports that all have to be linked to cell D16 based on the port selected in cell A14. All the formulas are on 'sheet4' along with the lists that for the drop-down menus.Hope this is clearer. Til I can figure out how to attach a copy of the worksheet.

Report •

#3
February 14, 2013 at 13:36:12
 You can not attach worksheets to posts in this forum. You can provide a link to a website where the sheet could be found, and if members of this forum choose to, they could go look at it.It seems to me that you should only need one formula, along with a table to pull in the minimums.Since you have not supplied the formula that you are using in C16, I'll have assume that you have a lookup table to populate C16 with a rate based on the port chosen from the drop down.Something like:=VLOOKUP(A14,Sheet2!\$A\$1:\$B\$26,2,0)Again, I'm guessing here, but let's say Sheet2!A1:B26 contains your ports (Column A) and rates (Column B). If that's right, then how about adding a third column (C) with the minimum charges for each port?In that case, your formula in D16 might be something like:=IF(A19*C16 How To Post Data or Code.

Report •

Related Solutions

#4
February 15, 2013 at 06:51:07
 Thanks a lot. You assumed just right. I am not too familiar with VLookup so many times, judging by the way VLookup works, I seem to have been doing a lot of unnecessary work when that method just simplifies it. Thanks again DerbyDad. You are an inspiration.

Report •

#5
February 15, 2013 at 07:10:48
 I'm glad I could help. Now I need to ask a favor of you.As I mentioned numerous times, I was making various assumptions when trying to offer a solution. Luckily (this time) my assumptions were right. Had they been wrong, I would have wasted a lot of time testing and typing. I don't like to post suggestions unless I've given them at least a cursory test. One misplaced parenthesis or operator could just cause more confusion, therefore I test my suggestions whenever possible.So, the next time you have a question in this or any other forum, please try to supply enough information so that the volunteers know what they are working with and assumptions can be kept to a minimum. In this case, the VLOOKUP formula would have helped since that was the basis for my suggestion.Thanks!Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#6
February 19, 2013 at 08:25:27
 Ok. I will try to suply as much info as possible. This time I have something similar but new. I have a sheet with varying ranges. Each value that is to be displayed, depends on a value entered in two other cells. For example: Cell A1 = Item Description (drop down list)Cell A2 = Item QuantityCell C5 = Cost per Item (varies depending on quantity)Cell D5 = Final calculated cost In Cell C5, some items have anywhere from 3 to 6 different values/costs depending on the amount being ordered. eg: 1-44 = 1.52 per item45-299 = 1.29 per item300-499 = 1.25 per item500-999 = 1.11 per item1000- = 0.92 per itemKeeping in mind as well that there are 13 different items, how do I get cell C5 to show the value depending on the item type in ceel a1 and the quantity in cell A2? Would this be a similar VLookup formula?

Report •