Results 1 to 8 of 8

Thread: drop down boxes and costings

  1. #1

    drop down boxes and costings



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    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
    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

  7. #7
    It is a formula that processes a range of cells ($B$16:$B$31) against a condition or value (the value in cell $B106 in this case). Every cell in that range that matches the condition/value gets counted. I pseudo-code, you might say

    For each cell in the range B16:B31
    If the cell value equals the value in cell B106
    increment counter;

    Return counter;

    When you copy the formula down,
    $B$16:$B$31 doesn't change because of all the $, but $B106 will increase the row number because there is no $ on the row number, the row number is relative.

  8. #8
    Quote Originally Posted by Bob Phillips View Post
    It is a formula that processes a range of cells ($B$16:$B$31) against a condition or value (the value in cell $B106 in this case). Every cell in that range that matches the condition/value gets counted. I pseudo-code, you might say

    For each cell in the range B16:B31
    If the cell value equals the value in cell B106
    increment counter;

    Return counter;

    When you copy the formula down,
    $B$16:$B$31 doesn't change because of all the $, but $B106 will increase the row number because there is no $ on the row number, the row number is relative.
    Thank you, this has been very useful and is much appreciated. Even numbers for ipad recognises it!

    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
  •