+ Reply to Thread
Results 1 to 28 of 28

Creating formula using multiple Data Validation values

  1. #1
    Registered User
    Join Date
    08-07-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    20

    Creating formula using 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 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
    Last edited by MichaelH2912; 08-07-2013 at 09:51 AM. Reason: Table Missing

  2. #2
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: Urgent Help needed- stuck creating formula using multiple Data Validation values

    Hi Mike
    Personally I can make no promises that I can help you but it would be much easier to see what you have now in order for me to assess if I can help you. Can you post what you have so far including all your formulae.
    Tony

  3. #3
    Registered User
    Join Date
    08-07-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Urgent Help needed- stuck creating formula using multiple Data Validation values

    Hi Tony,

    Thanks for the reply. I can put something together quickly to show you, including what I have so far, I'll just have to change the details slightly to not post any sensitive information! Gimme 5 minutes

  4. #4
    Registered User
    Join Date
    08-07-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Urgent Help needed- stuck creating formula using multiple Data Validation values

    I hope this works, I may need some help with uploading...
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-07-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Urgent Help needed- stuck creating formula using multiple Data Validation values

    It's really basic, I've just set it up as I want it to show based on the parameters of the first option (Board Type), but I need help with setting up an optimal layout to pull everything through based on both the main category and the sub category, and then the varying factors to create a final price (which I can do, I just don't know how to instruct excel which price to select based on the defined rules of selection)

    Also, I don't know if it is possible to have both a formula, and the option to free type in the same cell

    Thanks again for any help or advice

  6. #6
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: Urgent Help needed- stuck creating formula using multiple Data Validation values

    Hi
    I will take a look at your sample and probably get back to you sometime tomorrow.
    Tony

  7. #7
    Registered User
    Join Date
    08-07-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Urgent Help needed- stuck creating formula using multiple Data Validation values

    Thanks Tony, I appreciate it

  8. #8
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: Urgent Help needed- stuck creating formula using multiple Data Validation values

    Hi Mike
    Having looked at your requirements it is certainly feasible but it will require some extensive VBA to achieve the various permutations. Sadly I can do basic VBA but not to the level you are going to need.
    Sorry I cant help further.
    Tony

  9. #9
    Registered User
    Join Date
    08-07-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Urgent Help needed- stuck creating formula using multiple Data Validation values

    Hi Tony,

    Thanks for looking, I really appreciate it. As I'm a novice, just quickly can you give me a brief explanation on VBA?! I've never used this before :)

  10. #10
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: Urgent Help needed- stuck creating formula using multiple Data Validation values

    Hi Mike
    Basically VBA (Visual Basic for Application) is the language used for all Microsoft Application and is the language that is used when you record Macros from your keyboard.
    I could probably help you out with the various cell validations based on each permutation if that helps. If you can give all the permutations you need to deal with then I will see what I can do.
    Hope this helps.
    Tony

  11. #11
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: Urgent Help needed- stuck creating formula using multiple Data Validation values

    Hi Mike
    I have done some work on Stage 1 at the moment. Have a look at the attached and let me know if this is what you are after for Stage 1. There is VBA code sitting behind the worksheet that controls the various permutations. To look at the code right click on the tab name and select view code.
    Look forward to hearing from you.
    Tony
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    08-07-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Urgent Help needed- stuck creating formula using multiple Data Validation values

    Hi Tony,

    Thanks so much for looking into this for me. I don't know if I am doing something wrong, but when I look at the spreadsheet I cannot change any of the options from the validation lists, is this correct at the moment?

    The coding in the second tab looks correct to me - to spend too much time on it, it won't actually be the boards that would need costing - it'll be more fiddly stuff - this was just the first thing I thought that I could use as an example to perfect the formula. Once I know how to do it I will be able to apply it to everything that I need accurately.

  13. #13
    Registered User
    Join Date
    08-07-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Urgent Help needed- stuck creating formula using multiple Data Validation values

    Hi Tony,

    I managed to get the macro's enabled, but now I'm getting the following error message:

    Run-time error '1004':

    Unable to set the Locked property of the Range class


    ...any idea's what this means?

    I really do appreciate all the help,

    Mike

  14. #14
    Registered User
    Join Date
    08-07-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Urgent Help needed- stuck creating formula using multiple Data Validation values

    ...it's the red text below:


    Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)

    Select Case Target.Address

    Case "$B$5", "$D$5"
    'Insert your code here
    If Range("B5").Value = "MDF" And Range("D5").Value = "Coated" Or Range("B5").Value = "MDF" And Range("D5").Value = "Raw" Then
    Range("F5").Value = "Enter Size Below"
    Range("G5").Value = "Enter Size Below"
    Range("H5").Value = "Select Below"
    Range("F6").Select
    Else
    If Range("B5").Value = "MDF" And Range("D5").Value = "Laminated" Then
    Range("F5").Value = "Standard Only"
    Range("G5").Value = "Standard Only"
    Range("H5").Value = "Not Applicable"
    Range("F6").Value = ""
    Range("G6").Value = ""
    Range("H6").Value = ""
    Else
    If Range("B5").Value = "Steel" Then
    ' Range("D5").Value = Range("R5")
    Range("F5").Value = "Standard Only"
    Range("G5").Value = "Standard Only"
    Range("H5").Value = "Not Applicable"
    Range("F6").Select
    Else
    If Range("B5").Value = "PVC" Then
    ' Range("D5").Value = Range("R5")
    Range("F5").Value = "Enter Size Below"
    Range("G5").Value = "Enter Size Below"
    Range("H5").Value = "Not Applicable"
    Range("F6").Select
    End If
    End If
    End If
    End If
    If Range("F5").Value = "Standard Only" And Range("D5").Value = "Standard Only" Then
    Cells.Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    Range("F6:G6").Select
    Selection.Locked = True
    Selection.FormulaHidden = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End If
    If Range("F5").Value <> "Standard Only" And Range("D5").Value <> "Standard Only" Then
    Cells.Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    Range("F6:G6").Select
    Selection.Locked = True
    Selection.FormulaHidden = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End If

    Case Else
    'Insert code for all other cells (if needed, else leave blank)

    End Select

    End Sub
    Last edited by MichaelH2912; 08-09-2013 at 08:30 AM.

  15. #15
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: Urgent Help needed- stuck creating formula using multiple Data Validation values

    Hi Mike
    The error you are getting is to do with locking the size cells (F6 and G6) if the description is "Standard Only". I will take a look and get back to you.
    Tony

  16. #16
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: Urgent Help needed- stuck creating formula using multiple Data Validation values

    Hi Mike
    I have made some changes to the code and hopefully it works okay your end.
    Let me know.
    Tony
    Attached Files Attached Files

  17. #17
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: Urgent Help needed- stuck creating formula using multiple Data Validation values

    Hi Mike
    I have changed the VBA code slightly.
    Let me know how you get on.
    Tony
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    08-07-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Urgent Help needed- stuck creating formula using multiple Data Validation values

    Ah, brilliant. Thanks Tony.

  19. #19
    Registered User
    Join Date
    08-07-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Creating formula using multiple Data Validation values

    Hi Tony,

    I actually replied to your earlier message, but didn't realise you had messaged again (on page 2)! I've checked it out, it seems to work like a dream! My next question is - the way you have coded it means that you cannot type into the box when the parameters say "Standard only", but you can when they say "Enter size below", which is perfect. However, is there any way to set this so that if I type text into F6, then change the ruling so that F5 states "standard only", the text will be removed from F6?

    Thanks,

    Mike

  20. #20
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: Creating formula using multiple Data Validation values

    Hi Mike
    See attached update.
    Tony
    Attached Files Attached Files

  21. #21
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: Creating formula using multiple Data Validation values

    Hi Mike
    I have made some further changes to the VBA code to make it more efficient. I have also combined the Board and Sub Types to help the code operate more efficiently.
    Let me know what you think.
    Tony
    Attached Files Attached Files

  22. #22
    Registered User
    Join Date
    08-07-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Creating formula using multiple Data Validation values

    Hi Tony,

    Thanks for looking into this for me Tony - the last tweak seems to work a treat. Do you think it would be possible to make this work with the Board and Sub types being separated? The reason I ask is because on some of the calculators I want to create there will be a lot of different possible combinations; I wanted to try and make the main product drop down list as small as possible. However if this is not possible then I can compromise and find a way to work it the way you have suggested.

    Thanks again, you're a star

    Mike

  23. #23
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: Creating formula using multiple Data Validation values

    Hi Mike
    Whilst I am sure it would be possible to separate the two fields out as you requested it would take some serious VBA to do this as the code runs automatically on ANY changes to cell B5. Separating them out causes the code to crash as it gets stuck in a loop. I am sure someone with more extensive VBA skills than me "could eat this for breakfast" .
    I would suggest that when you have your final Board Type-Sub Type you put them in alphabetical order so that the user can scroll down to the relevant selection for their needs.
    Hope this helps.
    Tony

  24. #24
    Registered User
    Join Date
    08-07-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Creating formula using multiple Data Validation values

    Thanks Tony, I may see if I can get around it using your coding, as it seems to work quite well. I'm in the middle of playing around with your test sheet and coding, to see how effective I can make it, and get an idea as to how it would be visually etc. And then start working towards stages two & three.

    I'll upload it for you when I am done

  25. #25
    Registered User
    Join Date
    08-07-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Creating formula using multiple Data Validation values

    Here you go Tony, I've filled in a little bit more data, and tied it to stage 2.
    Attached Files Attached Files

  26. #26
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: Creating formula using multiple Data Validation values

    Hi Mike
    Just looking at your latest version.
    In F7 replace your formula with this one:
    =IF(OR(F6="",G6=""),"Sizes not yet entered",IF($F6<=2440,IF($G6<=1220,"Size OK!","Too large to quote!"),"Too large to quote!"))
    Tony

  27. #27
    Registered User
    Join Date
    08-07-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Creating formula using multiple Data Validation values

    Thanks Tony,

    This is the way I have it set up currently, but as I said I'm no excel wiz, so I'm open to suggestions. On my current calculator there is also parameter for "Too Small", and all three warning messages change colour (Yellow for "Too Small", Green for "Size Ok", and red for "Too Large")

  28. #28
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: Creating formula using multiple Data Validation values

    Hi Mike
    I have done some more work on the calculation for the pricing based on board type/sub type and GBP/Euro. Attached is the latest version. You will need to insert prices against the various board types under BOM as you will see to fully test it.
    Let me know if you need any further help.
    Tony
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 03-20-2013, 05:50 AM
  2. [SOLVED] Substituting multiple values for data validation
    By Chemistification in forum Excel General
    Replies: 6
    Last Post: 01-07-2013, 12:43 PM
  3. Replies: 9
    Last Post: 11-26-2012, 12:46 AM
  4. creating VB code using advanced search criteria with multiple data validation lists?
    By Jonathan Bay in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-14-2011, 09:56 AM
  5. Replies: 2
    Last Post: 09-26-2009, 08:18 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1