stuck with create formula include multiple Data Validation values

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

Do you have a spreadsheet to post with what you have so far?

Here you go, it's just a basic test version.

View attachment TEST version.xls

Thanks

Hello Michael
You have quite a difficult task in front of you. Unless you can get someone to develop some VB modules for you, you may have to compromise on some of the requirements.
Looking at your problem with the MDF board, its because this has two text options, and VLOOKUP can only provide one. In order to get there with one LOOKUP, you could extend the number of
records in your P:Z table to include more options (eg MDFC, MDFR and MDFL) so that you can create extra text messages. and then use the combined Board and Sub Type as your VLOOKUP.
See if that could work and I'll see if I can come up with anything on the other problems.

HTH

Hercules

Hi Mike,

For the first stage, you can achieve changing of text for laminated MDF board by using nested IFs, and additional entries in the data table.

in cell F5, change the formula to this:
=IF(ISNA(VLOOKUP($B$5&$D$5,$T:$U,2,0)),IF(ISNA(VLOOKUP($B$5,$T:$U,2,0)),"",(VLOOKUP($B$5,$T:$U,2,0))),(VLOOKUP($B$5&$D$5,$T:$U,2,0)))
it looks for B5 & D5 combined first, if it does not exist, then look only for B5.

And in your data table you would want to maintain data like this for all possible combinations:



As for second and third stages, it is very difficult for me to imagine how it should look like, or how do you want it to be displayed. You would need to provide or at least come up with a "final example", and with all the prices available, for each component etc.

Edit: sorry for horribly looking table I have no idea how to beautify that

Hey, thanks for this. I don't know if I am doing something wrong, but I added the MDF Laminated option in to my table, and it doesn't work or make a difference... But I like the idea of what you have done. Would the "MDFLaminated" part all go in one cell, or would it work if they are in two separate cells (in which case I would need to amend the formula)

"MDFLaminated" should be entered in a single cell, and should have no space inbetween.