+ Reply to Thread
Results 1 to 6 of 6

estimating spreadsheet

  1. #1
    Registered User
    Join Date
    03-23-2010
    Location
    California,USA
    MS-Off Ver
    Excel 2003
    Posts
    3

    estimating spreadsheet

    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?
    Last edited by SemperFidelis; 03-24-2010 at 10:19 AM.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: estimating spreadsheet

    You need to use Data Validation & VLOOKUP

    http://excel-it.com/data_validation.htm
    http://excel-it.com/excel_functions.htm
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Registered User
    Join Date
    03-23-2010
    Location
    California,USA
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: estimating spreadsheet

    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

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: estimating spreadsheet

    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.

  5. #5
    Registered User
    Join Date
    03-23-2010
    Location
    California,USA
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: estimating spreadsheet

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

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: estimating spreadsheet

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1