Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Limit entry in a cell based on another Cell (Excel 2002/3)

    Hi

    I need to limit an enrty in a cell based on the data contained in another cell

    Please see attached

    Many Thanks in advance

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Limit entry in a cell based on another Cell (Excel 2002/3)

    By "If C16 contains B1,B2,B3,B4", do you mean the literal text "B1,B2,B3,B4", or do you mean "If C16 contains B1 or B2 or B3 or B4" or do B1, B2, B3 and B4 refer to the contents of the cells B1 etc.?

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Limit entry in a cell based on another Cell (Excel 2002/3)

    Hi Hans

    Sorry, C16 will only contain one item either " B1 or B2 or B3 or B4 or C1 or C2 or C3 or C4" Literally the the tex "B1" etc

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Limit entry in a cell based on another Cell (Excel 2002/3)

    Select cell D16.
    Select Data | Validation.
    Select "Custom" in the Allow dropdown list.
    Enter the following formula in the Formula box:

    =IF(OR(C16="B1",C16="B2",C16="B3",C16="B4",C16="C1 ",C16="C2",C16="C3",C16="C4"),D16<=35%,TRUE)

    If A2:A9 always contains the list B1..C4, you can also use the simpler formula

    =IF(ISNA(MATCH(C16,A2:A9,0)),TRUE,D16<=35%)

    Activate the Error Alert tab and enter an appropriately insulting message, then click OK.

  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: Limit entry in a cell based on another Cell (Excel 2002/3)

    <hr>enter an appropriately insulting message<hr>

    Personally, I think once you decide to go with the "insulting message", I would rather pick the "inappropriate" ones <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    Steve

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Limit entry in a cell based on another Cell (Excel 2002/3)

    Hi Hans

    I have applied your custom formula in data validation, but it won't allow me to enter anything, I fear I have made an error somewhere

    I also made an error in the instruction in the worksheet If D16 containd any of the C's it should be a maximum of 30%.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Limit entry in a cell based on another Cell (Excel 2002/3)

    Hi Hans

    Sorry I attached the wrong Workbook.

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Limit entry in a cell based on another Cell (Excel 2002/3)

    1) Try this formula in Data | Validation:

    =IF(OR(C16="B1",C16="B2",C16="B3",C16="B4"),D16<35 %,IF(OR(C16="C1",C16="C2",C16="C3",C16="C4"),D16<= 30%,TRUE))

    2) You must enter a percentage, for example 23%. If you enter 23, that corresponds to 2300% so it won't be accepted.

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limit entry in a cell based on another Cell (Excel 2002/3)

    I think that the formula that you want is:

    <code>
    =D16<=VLOOKUP(C16,$A$2:$C$9,3,FALSE)
    </code>
    Legare Coleman

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Limit entry in a cell based on another Cell (Excel 2002/3)

    Hi Hans

    Thank you for your efforts It's almost there however it won't let me enter 35% against B1.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Limit entry in a cell based on another Cell (Excel 2002/3)

    Change <35% to <=35% in the formula.

  12. #12
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Limit entry in a cell based on another Cell (Excel 2002/3)

    Hi Hans

    Sorry to be a pain, but I still can't enter anything in D16.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  13. #13
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Limit entry in a cell based on another Cell (Excel 2002/3)

    HI legare

    Thanks for the reply but in this instance a lookup won't suffice because the user need to able to enter any percentage up to the maximum of 35% for B and 30% for C

    Many thanks

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Limit entry in a cell based on another Cell (Excel 2002/3)

    Please remove the space in front of the formula in Data | Validation. The space invalidates the entire formula.

  15. #15
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limit entry in a cell based on another Cell (Excel 2002/3)

    With the table that is in your worksheet, that is exactly what my formula does if you replace Hans' formula with it in Data Validation. Doesn't the attached workbook do what you have been asking?
    Legare Coleman

Page 1 of 2 12 LastLast

Posting Permissions

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