Can you post your workbook?
Re iPad, load it into SkyDrive, and you can browse with any web browser.
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
Can you post your workbook?
Re iPad, load it into SkyDrive, and you can browse with any web browser.
Hi, thanks for the reply. Trying to attach files now.
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!
Is this what you want
In E106, add the formula
=COUNTIF($B$16:$B$31,$B106)
and copy down.
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
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.
Bookmarks