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

Report •

February 14, 2013 at 10:01:32
Your question is not clear to me. The Data Validation list and the calculation in C16 seems fairly clear. I assume you have a VLOOKUP or something similar in C16.

What is not clear is what you mean by "in cell D16, there are varying "minimum" calculations that need to be arrived at".

How does the value in A14 and/or C16 relate to your requirements for D16? Keep in mind that we can't see your spreadsheet from where we're sitting, nor can we read minds, so you need to be specific about what you are trying to accomplish.

It often helps if you could provide some example data and the expected results based on that data. Please click on the following line and read the instructions found via that link before you post example data. Thanks!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

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:

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 •

February 14, 2013 at 13:36:12
✔ Best Answer
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:


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:


This formula would check to see if A19*C16 met the minimum listed in Column C. If it didn't, it would return the minimum from the table.

Again, I have to stress that without more specifics as to how you are pulling the data into C16, I'm making multiple assumptions with this suggestion.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

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 •

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.


Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

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 Quantity
Cell 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.
1-44 = 1.52 per item
45-299 = 1.29 per item
300-499 = 1.25 per item
500-999 = 1.11 per item
1000- = 0.92 per item

Keeping 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 •

Ask Question