Forum Discussion

TimothyE's avatar
TimothyE
Copper Contributor
Sep 18, 2019

Excel Text Validation Help

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. A...
Exel
validation
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 18, 2019

TimothyE 

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

  • TimothyE's avatar
    TimothyE
    Copper Contributor
    Sep 18, 2019

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

    • PReagan's avatar
      PReagan
      Bronze Contributor
      Sep 18, 2019

      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

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Sep 18, 2019

      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...

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Sep 18, 2019

        SergeiBaklan 

        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"

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

Resources