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

Exel
validation

5 Replies

    • 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

Resources