drop down boxes and costings

[Paul R]

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

[Bob Phillips]

Can you post your workbook?

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

[Paul R]

Hi, thanks for the reply. Trying to attach files now.

[Paul R]

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!

[Bob Phillips]

Is this what you want

In E106, add the formula

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

and copy down.

[Paul R]

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

[Bob Phillips]

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.

[Paul R]

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