| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 103432 | 2009-09-23 04:00:00 | Excel formula | bpt2 (6653) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 812968 | 2009-09-23 04:00:00 | I am using the following formula to validate input in 2 adjacent cells: =OR(AND(E5=0,D5=2), AND(E5=1,D5=2), AND(E5=2,D5=0),AND(E5=2,D5=1))). I want to also disallow any initial input over 2. How do I do this? |
bpt2 (6653) | ||
| 812969 | 2009-09-23 04:25:00 | I am using the following formula to validate input in 2 adjacent cells: =OR(AND(E5=0,D5=2), AND(E5=1,D5=2), AND(E5=2,D5=0),AND(E5=2,D5=1))). I want to also disallow any initial input over 2. How do I do this? In Excel 2007 you go to data>data validation>custom and type in =<2 and also put in a message of your choice. Do this for each cell you need to be less than or equal to 2. That would allow the entry -2 of course. |
Sweep (90) | ||
| 812970 | 2009-09-23 05:29:00 | How do I add that to the existing formula? | bpt2 (6653) | ||
| 812971 | 2009-09-23 05:45:00 | How do I add that to the existing formula? You don't add it to the formula. You use the data validation already built in. I take it you have two adjacent cells and you want to disallow certain input. Am I correct? |
Sweep (90) | ||
| 812972 | 2009-09-23 05:51:00 | Yes, but I am already using the formula =OR(AND(E5=0,D5=2), AND(E5=1,D5=2), AND(E5=2,D5=0),AND(E5=2,D5=1))) in data validation. | bpt2 (6653) | ||
| 812973 | 2009-09-23 07:10:00 | Yes, but I am already using the formula =OR(AND(E5=0,D5=2), AND(E5=1,D5=2), AND(E5=2,D5=0),AND(E5=2,D5=1))) in data validation. I take it the only allowed values will be 0, 1, or 2 in cells D5 and C5. Rather than the formula you have it would be easier to use this method. cyberskillsguide.com I hope I have now put you on the right track. |
Sweep (90) | ||
| 812974 | 2009-09-23 07:44:00 | Yes, but only in combinations: 2-0, 2-1, 1-2 and 0-2 | bpt2 (6653) | ||
| 812975 | 2009-09-23 10:30:00 | Yes, but only in combinations: 2-0, 2-1, 1-2 and 0-2 Well I'm still not sure what you are up to. The formula you have quoted is invalid anyway:- "Yes, but I am already using the formula =OR(AND(E5=0,D5=2), AND(E5=1,D5=2), AND(E5=2,D5=0),AND(E5=2,D5=1))) in data validation. " You probably need a series of IF statements. I will have a look at it tomorrow when I get more time. Or hopefully Parry will drop in. :confused: |
Sweep (90) | ||
| 812976 | 2009-09-23 14:04:00 | Thanks for your interest and help. | bpt2 (6653) | ||
| 812977 | 2009-09-23 22:31:00 | One the better things you'l do, join this group. EXCEL-L@PEACH.EASE.LSOFT.COM If they are anywhere near as helpful as the 'Access' Group Don |
donread (6401) | ||
| 1 2 | |||||