EXCEL 2007 help with building quote sheet

I am building a document for the company I work for to assist with getting customers quotes quicker. I have several things though that I have never done before that I need to figure out and would appreciate any help as I have been tasked with this job regardless of my knowledge.

1.) I need to have a drop down box with part names that when selected will pop up a price in a separate cell. I have the drop down box created using "Data Validation">List with the Source being a column of cells on worksheet 2. It's the second half of this I am struggling with. I had used an IF statement for one option that only had 3 variables with two of them being the same price. This worked for this situation but won't work to my knowledge on 3+ variables with 3+ prices. The formula I used was (primary distribution is the name of the grouping:

=IF(A14="primary distribution","$1,000","$750")


The second thing I need to be able to do is have a 4x3 table with the following headers:

Qty.
Part Number & Description
Single Unit Cost
Extended Cost

The quantity can be <blank>,1, or 2 but the sum of the two rows can not exceed 2.

the part number & description is a drop down with 3+ variables.

the cost will be filled in automatically based on the single unit cost from the part number & description column.

the extended cost will be filled in automatically based on quantity x single unit cost




Thanks in advance for any help or direction with this as I am pretty lost. If something is not clear let me know and I am happy to provide more details.

It sounds like an Index and Match function might work for you to pull the price of a part name selected. Look at the formula below. It would be placed into the cell that you want to return the price of a part based on the name selected in the Data Validation dropdown. Otherwise, it might help, and you would most likely get more people to provide help if you post an example of your workbook.

=INDEX(RangeWherePriceIsLocated,MATCH(DataValidationCell,RangeWhereDataInValidationCellWillBeFound,0))

perhaps if you added some examples of what your expected outcome is, and how you arrived at that, it would be easier to offer some suggestions to you?

Hi percussionman11,

I'm not sure if I understsand what you're trying to accomplish on the first part. Do the "primary Prices" on Sheet1 column C directly correlate to the Primary items listed in Sheet1 column B? In other words, if you pick "Option A" from the Special-Order Pricing sheet A14, are you expecting for Special-Order Pricing D14 to populate with $1000? And if "Option B" or "Option C" is selected then $750 will be returned in D14? Will there be more options with each having other pricing?

Also, I think for the First, Second and main columns, you are wanting the dropdowns to operate based on selections from above and the drop down lists to change the selections available because of what has been selected above. Is that correct?

Percussionman - I have the primary, first and second column figured out but I need to know how the "Main" dropdown fits in. What is it used for? Is it separate from the three above it?

I've attached a copy of what I have so-far. Let me know if the first 3 drop downs are what you needed or not.

Hi percussionman11,

I think I have everything the way you want it. Please take a look at the attachment. It is now a macro enabled workbook. Let me know if you need help understanding how to add more items for each list on Sheet 2.

Yeah, I wasn't sure how you needed the receptacles to be. I did them the same as the first, second and main drop downs as being dependant on the primary dropdown value. I may need more explanation of how the receptacles are supposed to work, how they related to the primary, etc.

I fixed the 2nd receptacle dropdown. Sorry about that... See the attached.

Glad you like the warning and reset features. I honestly couldn't think of any other way to do the warning or any other way to stop someone from entering more than the sum of 2 for them both.

Ah, I get it. I made the change for that and it's reflected in the new attachment.

I'm glad to be able to help. :-) Usually, I'm in here getting the help and it's not often I'm able to provide it myself. It was fun, in a geeky sort of way. The cascading menu system can be quite confusing. It took me a while to figure it out the first time I needed to use it. If you need help undertsanding what to do to add to, or subtract from each available list on sheet 2, let me know. You might want to lock both sheets and password protect them so changes can't be made that are unwanted. You might also want to create a copy as a backup of the original file.... just in case....

Enjoy!

P.S. - If this attachment is what you needed and you are satisfied with the outcome, please mark your post as solved.