Include a value from one cell within text of another cell.

[CedarHillMatt]

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.

 

[Pete_UK]

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

 

[Lee Li Fong]

You can also try this formula to cell B5,
=IF(AND(25<B4,B4<150),B4 &" is not a valid entry. Please enter the correct pair number.",""

You can also use Data Validation to retrict entry of number between 25 to 150

If not clear, can see the details at http://smartusemicrosoftoffice.blogspot.com/2010/04/include-value-from-one-cell-within-text.htm

Lee Li Fon
http://smartusemicrosoftoffice.blogspot.com