Sep 18 2019 12:50 PM
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
B9, B11, B13 Date Validation:
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"))))))))))))))))))))))))))))
Sep 18 2019 12:59 PM
Why don't you simply use =C8 in Maximum bar?
Sep 18 2019 01:06 PM
@Sergei Baklan I believe C8 is attempting return the entire formula in that cell. It appears =value(C8) is producing the intended results.
Sep 18 2019 01:14 PM
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...
Sep 18 2019 01:28 PM
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
Sep 18 2019 01:52 PM
Yes, exactly, right way is to have dim range like this
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"
))))))))))))))))))))))))))))