estimating spreadsheet

**SemperFidelis** · 03-23-2010, 03:02 PM

I'm working on an estimating spreadsheet, I want to have in column A the generic product,for instance a "well seal", I'd like to be able to have a drop down list that lists all our different sizes of well seals, the current "cost of goods" price showing in column B and the marked up price showing in column C. I would also like to be able to print out a version of the estimate that will only show those products we've selected without the prices so the techs that are going out and servicing the customers can gather their products without knowing what we paid for them or what we're charging the client. Can excel do this? And if so ...How?

**royUK** · 03-23-2010, 03:14 PM

You need to use Data Validation & VLOOKUP

http://excel-it.com/data_validation.htm
http://excel-it.com/excel_functions.htm

**SemperFidelis** · 03-23-2010, 09:39 PM

Thanks for the info...I've learned the basic data validation, however, the vlookup is Greek to me and from what I'm seeing from the examples I'm not sure it's what I need. I want to be able to choose a part, it have a drop down list that will select the part and then in the adjoining cell have the wholesale price and then in the next cell have the retail price, I need to be able to total the retail price at the end of the estimating sheet...for instance, we have 30 different well seals, if I have to list every well seal on the estimating sheets along with the other 1000's of parts, my estimating sheet is going to be 50 pages long, if my estimating sheet had the part catagory and then drop downs for the actual part plus drop downs for the wholesale and retail prices, my life at work just got alot easier, but how do you tie a part number to a wholesale price and retail price that is constantly changing,...can this be done? my apologies for rambling...I'm not sure how to phrase the question

**JBeaucaire** · 03-23-2010, 09:46 PM

I use INDEX/MATCH for this, but you can also use VLOOKUP() or LOOKUP() based on some other restrictions about the layout of the table you're searching. INDEX/MATCH has none of those restrictions.

Here's some sample sheets showing what I think you're looking to do.

INDEX/MATCH Examples

These sheets show multiple examples of pulling data from table based on one or two matching criteria.

**SemperFidelis** · 03-23-2010, 09:54 PM

I feel like I won the lottery...thank you so much!

**royUK** · 03-24-2010, 02:28 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Note: the exampleon the VLOOKUP page should be easy to adapt for what you want