drop down boxes and costings

Paul R

New member
Joined
Feb 27, 2013
Messages
5
Reaction score
0
Points
0
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

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Can you post your workbook?

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

Paul R

New member
Joined
Feb 27, 2013
Messages
5
Reaction score
0
Points
0
Hi, thanks for the reply. Trying to attach files now.
 

Attachments

  • IPAD 4 DROP MENU TEMPLATE Intruder costings 250213.xlsm
    287.2 KB · Views: 38

Paul R

New member
Joined
Feb 27, 2013
Messages
5
Reaction score
0
Points
0
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

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Is this what you want

In E106, add the formula

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

and copy down.
 

Paul R

New member
Joined
Feb 27, 2013
Messages
5
Reaction score
0
Points
0
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

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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

New member
Joined
Feb 27, 2013
Messages
5
Reaction score
0
Points
0
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
 
Top