Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

multiple if statements

0

I wanted to know if there is something quicker than using multiple if statements. I have a quote sheet for work were the estimator would plug in qty and types all linked ot a price on one tab/sheet. Then the client would see a quote with only the options the estimator has choosen. 

I don't mid doing more research just wasn't sure if there is a different formula to do this.

I only have couple lines in the description area for the items selected on the previous tab. So the first line in the decription I would have to have

=if item 1 is empty then put item 2 if item 2 is empty then put item three. There are probably around 20 different items that can be selected and some are drop down items.

Any help would be greatly appreciated.

Answer
Discuss

Answers

0

the question is somewhat vague.  But I would have you check into either vLookup or Select Case Statements.

Take 2:  I would go with a template (see attached). that uses a validation table (so you can only pick your items from the list!) and have lookup formulas off of the Validation cell that would pick up the Description and the Price.  If you do not need lines you can either hide or delete them.

I would keep the template in tact and copy the invoice to its own tab, this way you can reuse over and over.  Note: the invoice was borrowed from the internet....the 2nd tab has the items and the lookups for the Description and price.  Please investigate it first.  you may need to adjust your items and hence the Validation list.  I should have added another IF statement for the Unit Price (so if it is Blank ~ it could have a message "you need to enter an item" or something close to it.  This should give you some ideas of how you could construct your template.  Depending on the size of your info, you may want to consider Access?  The Northwind database is a good example.

Good Luck.

Discuss

Discussion

Hi Queue, Thanks for the reply. I guess what I am looking for is if you had a basic quote sheet, company name at the top client name address ect. You then would have a couple line items in the description to write out the procedures you are quoting on and then a price each line item spelled out. I am using index,match, and vlookup to pull the desription items from the estimators tab to the client quote sheet. The problem I have is I was pulling info to each line item individually with a formula looking to 1 specific spot and if it has a number on the estimators sheet it would move it to the clients sheet.

What happens when there is nothing in that spot or line item, instead of an empty space I would like the next item to move up on the description area of teh client sheet.

Example:
Description                         Price
Color Match                         $2.50
Shrink wrap Package           $7.00
Reflash                                 $4.00

The items under the desription area will only have 8-9 spots but there are 20+ options on the estimators sheet. All 20 would never be used at once so I am trying to find a way to add the option to pull any of the options selected on the estimators sheet to the description area but without blank spaces. In the example above what id there was no Shrink wrap I would want the reflash to move up.

Hope that makes a little more sense. Again thank you for your help.
jminrod (rep: 15) Aug 30, '16 at 7:30 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login