Prev: Concatenation and Formatting
Next: Coping part of a cell content into a seperate cell
From: CedarHillMatt on 14 Apr 2010 10:48
In my spreadsheet, users are prompted to enter a value into cell B4. Multiple
peices of information are then returned based on what they entered, But
certain numbers are not valid (for instance 25 through 150). If an invalid
value is entered, I would like cell B5 to return something like:

" X is not a valid entry. Please enter the correct pair number."

I would like the cell to actually reference B4 so the number they typed will
appear where the "X" is. The only way I know how to do this would be to make
B5 equal another cell (lets say Z1).
Z1 would then contain =IF(B4=25," 25 is not a valid entry. Please enter the
correct pair number.",Z2)

Then Z2 would contain a similar formula for if they entered 26. There has
got to be a better way to do this, but I don't know what it is.

Thanks for your help.
From: Pete_UK on 14 Apr 2010 11:23
If all numbers from 25 to 150 inclusive are invalid, then you could do
this in B5:

=IF(AND(B4>=25,B4<=150),B4&" is not a valid entry etc",B4)

Hope this helps.

Pete

On Apr 14, 3:48 pm, CedarHillMatt
<CedarHillM...(a)discussions.microsoft.com> wrote:
> In my spreadsheet, users are prompted to enter a value into cell B4. Multiple
> peices of information are then returned based on what they entered, But
> certain numbers are not valid (for instance 25 through 150). If an invalid
> value is entered, I would like cell B5 to return something like:
>
> " X is not a valid entry. Please enter the correct pair number."
>
> I would like the cell to actually reference B4 so the number they typed will
> appear where the "X" is. The only way I know how to do this would be to make
> B5 equal another cell (lets say Z1).
> Z1 would then contain =IF(B4=25," 25 is not a valid entry. Please enter the
> correct pair number.",Z2)
>
> Then Z2 would contain a similar formula for if they entered 26. There has
> got to be a better way to do this, but I don't know what it is.
>
> Thanks for your help.

 | 
Pages: 1
Prev: Concatenation and Formatting
Next: Coping part of a cell content into a seperate cell