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

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

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

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

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

Hi Hans

Sorry I attached the wrong Workbook.

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

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

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

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

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

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

14. 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. 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?

Page 1 of 2 12 Last

Posting Permissions

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