Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Formatting or other method (2003 SP2)

    Good evening

    I am trying to build a worksheet to make an insurance quote form, on the worksheet I have 2 option buttons in a group box, 1 is Medical Goods and the other is General Goods, there is another cell with value in UK£, we can insure Medical Goods up to a value of £10,000 and General Goods up to £250,000 before we have to make a call to the Insurance brooker.

    I have been trying to make a Custom validation in a cell to say that if option 1 is selected and cell D17 is >10,000 "Exceeds Insurance value, call for quote" and if option 2 is selected and cell D17 is > £250,000 "Exceeds Insurance value, call for quote"

    Needless to say I am, as usual, floundering miserably

    Any ideas please (even starting again is an option!!)

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional Formatting or other method (2003 SP2)

    If the linked cell for the option group is A1 (adjust accordingly)

    =OR(AND(A1=1,D17<=10000),AND(A1=2,D17<=250000))

    Steve

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional Formatting or other method (2003 S

    I have attached the workbook but the basic thing to do is use the Forms toolbar and link the option group to a cell, I have used E1 for this purpose.

    Option button 1 provides the value 1 in E1 and button 2 provides value 2, this allows me to use an IF OR coupling:

    =IF(AND(E1=1,D17>10000),"Exceeds Insurance value, call for quote",IF(AND(E1=2,D17>150000),"Exceeds Insurance value, call for quote",""))
    Jerry

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting or other method (2003 S

    Fantastic

    Thanks Jezza and Steve, I prefer Jezza's answer because it gives the message as well, and above all I understand how it was done!!

    Thank you both for your input

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional Formatting or other method (2003 S

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> I forgot to mention that this was the formula to use for datavalidation.

    Here is a spreadsheet.

    Steve
    Attached Files Attached Files

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional Formatting or other method (2003 S

    That is cute Steve <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    Just a small thing and part of my learning experience, it seems to flag up on all errors including when I put text in it, which would make it nonsense. I am assuming Steve Hocking would be using the target cell as part of an accumulation.

    Can you suggest how we can assure that only numeric values are entered?
    Jerry

  7. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting or other method (2003 S

    Thanks very much Steve for that

    As usual you guys put me in a quandry, which of the excellent responses to use!!

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  8. #8
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting or other method (2003 SP2)

    Good Morning

    Going on a little, I must, As Jezza Pointed out, use the result in another calculation which I currently do like this

    =IF(D16=1,D24*0.02+30,IF(D16=2,D24*0.02+30))

    How can I introduce the 'Error Message' for the >10000 and >250000 in the above and can it be in the same cell as the calculation?

    Cheers as usual

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Formatting or other method (2003 SP2)

    You perform the same calculation in both cases <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Does this do what you want?

    =IF((D16=1)*(D24*0.02+30>10000)+(D16=2)*(D24*0.02+ 30>250000),"Dire warning",D24*0.02+30)

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Conditional Formatting or other method (2003 SP2)

    If you have D16 validated so it's always 1 or 2, you could shorten Hans' formula slightly to:
    <code>=IF(D24*0.02+30>CHOOSE(D16,10000,250000),"Di re warning",D24*0.02+30)</code>
    FWIW
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional Formatting or other method (2003 S

    Data Validation is very limited in what it can do. AFAIK, there is only one error message for the validation so it flags for any error. You could expand the error message to indicate the other types of error, if desired. ("You have entered invalid data. The entry is either not a number or the number exceeds the insurance value and you should call for a quote.")

    If you wanted different error messages based on different entries in the cell, you could use a message box triggered by a change event and the have the change event do the validation. You could have a message: "Please enter a number, text is not valid", "The value you entered (15,000) is too high for medical (Limit is 10,000). Please call for a quote" or "The value you entered (300,000) is too high (Limit is 250,000). Please call for a quote", or whatever else is appropriate.

    Steve

Posting Permissions

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