Hi All,
I'm new here, I decided to sign up for some help as I'm not too great on Excel. I can do some things, and work with some of the basic formula's, but it is all through trial and error, and not any training unfortunately.
I’m trying to create a calculator at work for working out custom pricing – we have these currently, but they are very basic. I don’t know if it is possible to achieve what I would like to achieve, but I will explain anyway, and I’m sure the experts (you guys!) will be able to tell if it is feasible or not, and what I should do to get where I need to.
For example, if we were selling boards, I am trying to arrange my calculator as so:
First Stage
BOARD TYPE SUB TYPE HEIGHT WIDTH COLOUR CURRENCY
(data list) (data list) Variable Text Variable Text Variable Text (data list)
(blank) (blank) To be typed To be typed (data list) (blank)
Board Type is selected from a Data Validation list, and are options such as MDF, STEEL, PVC.
Sub Type is also selected from a Data Validation List, and are options such RAW, COATED, LAMINATED etc
Height & Width are standard text, which is determined based on the value of the first two columns, i.e, MDF boards are fully customisable, Steel boards cannot be customised, and PVC boards can be customised height-wise, but not width-wise. So the texts which are bought into these boxes will read either “Standard Only” or “Enter Size Below”. However, where it gets complicated, is if an MDF board is laminated, it is only available in standard sizes, so the text should change from “Enter size below” to “Standard only”. I have hundreds of these types of combinations to work through – (I have managed to get it working based on just the Board type, but cannot get it to work based on the values of both Board type and Sub type)
The Colour option works the same as the height and width column, in which it will display a message based on both the Board type and Sub type.
The Currency is selected from a Data Validation list, with the option of either GBP or Euro.
The third line of the table only uses the height, width and colour columns. Height and Width are just to be typed in – however, I wanted to know whether there is a way to make these boxes unavailable when the option in the second box is “Standard Only”?
The colour options are just selected from a Data Validation list, but again, if possible I would like it to only be available when the text above states “Select Below”, but not when it says “Standard only”.
Second Stage
The second stage of this calculator is calculating the pricing. Somewhere below the main body of text and the calculator, there are Billing of Materials tables to calculate pricing for the different variations. I would like, again if possible, to have just one cell for the pricing, but I am struggling to get the formula right for this.
Imagine the BOMS calculate the prices based on the values of the above, which would get several different prices based on different specifics. If we call the table location C4 – H6… I would then need my formula to basically be:
The results of C5, D5, E6, F6, & H6 (wherever applicable) to dictate which cell on the BOM the price is selected from, then either shown as is with a £ sign (if GBP is selected), or a € sign (if Euro is selected).
Third Stage
The third and final stage of this calculator is a text box which makes up the body of the quotation to be sent to our customers, so it can be easily copy and pasted onto an email/word document. There are elements of the text which auto-fill based on the results of the selections in the table used in the first stage. This part I think I know how to do, but I’m not too worried about it at the moment. I have currently got it working as a cell fill, but would like it to tie together a bit neater.
Anyway, that comes later, right now I need to know if I can’t do the above because I’m no excel wiz, or if I can’t figure it out because it actually isn’t possible.
Any help or advice would be greatly appreciated. And if you can put it in layman’s terms for that would be even better!
Thanks in Advance,
Mike
Bookmarks