Results 1 to 8 of 8

Thread: drop down boxes and costings

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1

    drop down boxes and costings

    Hello. I have a customer-facing quote sheet spreadsheet with a drop down selection in 16 cells (B16 to B31). When I click on one I can select from a list of 20 products (using Data Validation list), and it will return a price by automatically putting a '1' on the costing sheet price list, the sum total of the prices will then appear on the customer facing page. I can select an item more than once.

    I have achieved this by having a huge amount of boxes with a lot of '1's and 'FALSE's on, linking back to the 16 cells individually, put there must be a simpler way. Looks like vlookup might help, but I know nothing about that!

    I want it to work a bit like the Sage type quotation sheets.

    One other complication, I need to load it on to my ipad using Numbers, so it needs to be simple (I know the numbers do not do Data Validation and the drop boxes do not translate, but I can do that manually on the ipad later).

    Thank you in anticipation.

    Paul

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,859
    Articles
    0
    Excel Version
    O365
    Can you post your workbook?

    Re iPad, load it into SkyDrive, and you can browse with any web browser.

  3. #3
    Hi, thanks for the reply. Trying to attach files now.
    Attached Files Attached Files

  4. #4
    Ahh, that did it. If you would be good enough to look at the first sheet 'intruder prices', that has the drop downs on it. B16 to B31 are the cells I am trying to simplify.

    Thank you for you help!

  5. #5
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,859
    Articles
    0
    Excel Version
    O365
    Is this what you want

    In E106, add the formula

    =COUNTIF($B$16:$B$31,$B106)

    and copy down.

  6. #6
    Hi Bob, that is inspired, thank you! It works a treat, I now need to adapt it to the other boxes.

    Just one thing, I am not exactly sure why it works though? Is there a simple explanation? Might help me adapt it to the rest of my drop down boxes easier.

    Thank you for your help, it is much appreciated.

    Regards

    Paul

Posting Permissions

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