Results 1 to 7 of 7

Thread: stuck with create formula include multiple Data Validation values

  1. #1

    stuck with create formula include multiple Data Validation values



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

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

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

    TEST version.xls

    Thanks

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    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

  5. #5
    Acolyte millz's Avatar
    Join Date
    Aug 2013
    Location
    Singapore
    Posts
    32
    Articles
    0
    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:

    Height
    MDF
    Enter Size Below
    Steel
    Standard Only
    PVC
    Enter Size Below
    MDFLaminated
    Standard Only


    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

  6. #6
    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)

  7. #7
    Acolyte millz's Avatar
    Join Date
    Aug 2013
    Location
    Singapore
    Posts
    32
    Articles
    0
    "MDFLaminated" should be entered in a single cell, and should have no space inbetween.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •