Excel Text Validation Help

Copper Contributor

I've been working on a excel form for one of our businesses and I'm attempting to use data validation to limit characters in 3 lines of text using =CELL("contents", C8) etc.. but it is not working. Any idea why this is? 

B9, B11, B13 are the cells with validation based on the out come of the C8, C10, C12 Formula

clipboard_image_0.png

 

B9, B11, B13 Date Validation:

clipboard_image_1.png

 

Formula for C8, C10, C12:

=IF(B3="Ebony_Urn","38",IF(B3="Preserve_a_Memory","12",IF(B3="Woodspun_Urn","12",IF(B3="Paw_Print_Memory_Box","38",IF(B3="Camel_Shell_Urn","12",IF(B3="Garden_Rock_Urn","12",IF(B3="Companion_Urn","38",IF(B3="Paw_Print_Rock_Urn","12",IF(B3="Granite_Marker","38",IF(B3="Cat_Figurine_Urn","12",IF(B3="Camouflage_Urn","12",IF(B3="Blue_Slate_Classic_Urn","38",IF(B3="Brown_&_Gold_Cloisonne_Urn","12",IF(B3="Silver_Rose_Urn","12",IF(B3="Contemporary_Urn","38",IF(B3="Classic_Urn","12",IF(B3="Heart_Memento_Urn","8",IF(B3="Crystal_Keepsake","12",IF(B3="Friendship_Photo_Urn","38",IF(B3="Rotating_Photo_Urn","38",IF(B3="Vertical_Photo_Urn","38",IF(B3="Horizontal_Photo_Urn","38",IF(B3="Friendship_Wood_Urn","38",IF(B3="Deluxe_Wind_Chimes","12",IF(B3="Dog_Tag_Necklace","8",IF(B3="Glass_Oval_Ornament","12",IF(B3="Deluxe_Wood_Urn","38",IF(B3="Standard_Wood_Urn","38"))))))))))))))))))))))))))))

5 Replies

@TimothyE 

Why don't you simply use =C8 in Maximum bar?

@Sergei Baklan I believe C8 is attempting return the entire formula in that cell. It appears =value(C8) is producing the intended results.

@TimothyE 

Yes, since you return texts =VALUE(C8) or =--C8

At the same I'm not sure you need texts, you may return numbers, e.g. instead of

=IF(B3="Ebony_Urn","38"...

use

=IF(B3="Ebony_Urn",38...

Hello @TimothyE,

 

An additional note. Your If() statement is rather lengthy. I would suggest creating a new worksheet with the Item in column A and the character lengths in column B. Then refer to this worksheet in cells C8, C10, and C12 as follows:

 

=Index(Sheet2Array, Match($B$3,Sheet2Array[Item],0),1)

 

If you want to restrict customers from accessing this worksheet, you may hide it and/or protect the worksheet.

 

Hope this helps!

PReagan

@Sergei Baklan 

Yes, exactly, right way is to have dim range like this

image.png

Another point, if use long formulas much better to apply some formatting, otherwise it's practically impossible to maintain. Something like this

=
IF(B3="Ebony_Urn","38",
IF(B3="Paw_Print_Memory_Box","38",
IF(B3="Granite_Marker","38",
IF(B3="Companion_Urn","38",
IF(B3="Blue_Slate_Classic_Urn","38",
IF(B3="Contemporary_Urn","38",
IF(B3="Friendship_Photo_Urn","38",
IF(B3="Rotating_Photo_Urn","38",
IF(B3="Vertical_Photo_Urn","38",
IF(B3="Horizontal_Photo_Urn","38",
IF(B3="Friendship_Wood_Urn","38",
IF(B3="Deluxe_Wood_Urn","38",
IF(B3="Standard_Wood_Urn","38",

IF(B3="Preserve_a_Memory","12",
IF(B3="Woodspun_Urn","12",
IF(B3="Camel_Shell_Urn","12",
IF(B3="Garden_Rock_Urn","12",
IF(B3="Paw_Print_Rock_Urn","12",
IF(B3="Cat_Figurine_Urn","12",
IF(B3="Camouflage_Urn","12",
IF(B3="Brown_&_Gold_Cloisonne_Urn","12",
IF(B3="Silver_Rose_Urn","12",
IF(B3="Classic_Urn","12",
IF(B3="Crystal_Keepsake","12",
IF(B3="Deluxe_Wind_Chimes","12",
IF(B3="Glass_Oval_Ornament","12",

IF(B3="Heart_Memento_Urn","8",
IF(B3="Dog_Tag_Necklace","8"

))))))))))))))))))))))))))))