Home

Excel Text Validation Help

%3CLINGO-SUB%20id%3D%22lingo-sub-861888%22%20slang%3D%22en-US%22%3EExcel%20Text%20Validation%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-861888%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20been%20working%20on%20a%20excel%20form%20for%20one%20of%20our%20businesses%20and%20I'm%20attempting%20to%20use%20data%20validation%20to%20limit%20characters%20in%203%20lines%20of%20text%20using%20%3DCELL(%22contents%22%2C%20C8)%20etc..%20but%20it%20is%20not%20working.%20Any%20idea%20why%20this%20is%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EB9%2C%20B11%2C%20B13%20are%20the%20cells%20with%20validation%20based%20on%20the%20out%20come%20of%20the%20C8%2C%20C10%2C%20C12%20Formula%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F132428iED6DD789699FA33D%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EB9%2C%20B11%2C%20B13%20Date%20Validation%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F132429iEE95027FBCF92D10%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_1.png%22%20title%3D%22clipboard_image_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EFormula%20for%26nbsp%3BC8%2C%20C10%2C%20C12%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3DIF(B3%3D%22Ebony_Urn%22%2C%2238%22%2CIF(B3%3D%22Preserve_a_Memory%22%2C%2212%22%2CIF(B3%3D%22Woodspun_Urn%22%2C%2212%22%2CIF(B3%3D%22Paw_Print_Memory_Box%22%2C%2238%22%2CIF(B3%3D%22Camel_Shell_Urn%22%2C%2212%22%2CIF(B3%3D%22Garden_Rock_Urn%22%2C%2212%22%2CIF(B3%3D%22Companion_Urn%22%2C%2238%22%2CIF(B3%3D%22Paw_Print_Rock_Urn%22%2C%2212%22%2CIF(B3%3D%22Granite_Marker%22%2C%2238%22%2CIF(B3%3D%22Cat_Figurine_Urn%22%2C%2212%22%2CIF(B3%3D%22Camouflage_Urn%22%2C%2212%22%2CIF(B3%3D%22Blue_Slate_Classic_Urn%22%2C%2238%22%2CIF(B3%3D%22Brown_%26amp%3B_Gold_Cloisonne_Urn%22%2C%2212%22%2CIF(B3%3D%22Silver_Rose_Urn%22%2C%2212%22%2CIF(B3%3D%22Contemporary_Urn%22%2C%2238%22%2CIF(B3%3D%22Classic_Urn%22%2C%2212%22%2CIF(B3%3D%22Heart_Memento_Urn%22%2C%228%22%2CIF(B3%3D%22Crystal_Keepsake%22%2C%2212%22%2CIF(B3%3D%22Friendship_Photo_Urn%22%2C%2238%22%2CIF(B3%3D%22Rotating_Photo_Urn%22%2C%2238%22%2CIF(B3%3D%22Vertical_Photo_Urn%22%2C%2238%22%2CIF(B3%3D%22Horizontal_Photo_Urn%22%2C%2238%22%2CIF(B3%3D%22Friendship_Wood_Urn%22%2C%2238%22%2CIF(B3%3D%22Deluxe_Wind_Chimes%22%2C%2212%22%2CIF(B3%3D%22Dog_Tag_Necklace%22%2C%228%22%2CIF(B3%3D%22Glass_Oval_Ornament%22%2C%2212%22%2CIF(B3%3D%22Deluxe_Wood_Urn%22%2C%2238%22%2CIF(B3%3D%22Standard_Wood_Urn%22%2C%2238%22))))))))))))))))))))))))))))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-861888%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Evalidation%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-861897%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Text%20Validation%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-861897%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F411366%22%20target%3D%22_blank%22%3E%40TimothyE%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhy%20don't%20you%20simply%20use%20%3DC8%20in%20Maximum%20bar%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-861916%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Text%20Validation%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-861916%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BI%20believe%20C8%20is%20attempting%20return%20the%20entire%20formula%20in%20that%20cell.%20It%20appears%20%3Dvalue(C8)%20is%20producing%20the%20intended%20results.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-861925%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Text%20Validation%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-861925%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F411366%22%20target%3D%22_blank%22%3E%40TimothyE%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%2C%20since%20you%20return%20texts%20%3DVALUE(C8)%20or%20%3D--C8%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EAt%20the%20same%20I'm%20not%20sure%20you%20need%20texts%2C%20you%20may%20return%20numbers%2C%20e.g.%20instead%20of%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3DIF(B3%3D%22Ebony_Urn%22%2C%2238%22...%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3Euse%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3DIF(B3%3D%22Ebony_Urn%22%2C38...%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-861945%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Text%20Validation%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-861945%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F411366%22%20target%3D%22_blank%22%3E%40TimothyE%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAn%20additional%20note.%20Your%20If()%20statement%20is%20rather%20lengthy.%20I%20would%20suggest%20creating%20a%20new%20worksheet%20with%20the%20Item%20in%20column%20A%20and%20the%20character%20lengths%20in%20column%20B.%20Then%20refer%20to%20this%20worksheet%20in%26nbsp%3Bcells%20C8%2C%20C10%2C%20and%20C12%20as%20follows%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIndex(Sheet2Array%2C%20Match(%24B%243%2CSheet2Array%5BItem%5D%2C0)%2C1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20want%20to%20restrict%20customers%20from%20accessing%20this%20worksheet%2C%20you%20may%20hide%20it%20and%2For%20protect%20the%20worksheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps!%3C%2FP%3E%3CP%3EPReagan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-861983%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Text%20Validation%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-861983%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%2C%20exactly%2C%20right%20way%20is%20to%20have%20dim%20range%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20264px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F132450iAE4B4A452E86CE44%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EAnother%20point%2C%20if%20use%20long%20formulas%20much%20better%20to%20apply%20some%20formatting%2C%20otherwise%20it's%20practically%20impossible%20to%20maintain.%20Something%20like%20this%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3D%0AIF(B3%3D%22Ebony_Urn%22%2C%2238%22%2C%0AIF(B3%3D%22Paw_Print_Memory_Box%22%2C%2238%22%2C%0AIF(B3%3D%22Granite_Marker%22%2C%2238%22%2C%0AIF(B3%3D%22Companion_Urn%22%2C%2238%22%2C%0AIF(B3%3D%22Blue_Slate_Classic_Urn%22%2C%2238%22%2C%0AIF(B3%3D%22Contemporary_Urn%22%2C%2238%22%2C%0AIF(B3%3D%22Friendship_Photo_Urn%22%2C%2238%22%2C%0AIF(B3%3D%22Rotating_Photo_Urn%22%2C%2238%22%2C%0AIF(B3%3D%22Vertical_Photo_Urn%22%2C%2238%22%2C%0AIF(B3%3D%22Horizontal_Photo_Urn%22%2C%2238%22%2C%0AIF(B3%3D%22Friendship_Wood_Urn%22%2C%2238%22%2C%0AIF(B3%3D%22Deluxe_Wood_Urn%22%2C%2238%22%2C%0AIF(B3%3D%22Standard_Wood_Urn%22%2C%2238%22%2C%0A%0AIF(B3%3D%22Preserve_a_Memory%22%2C%2212%22%2C%0AIF(B3%3D%22Woodspun_Urn%22%2C%2212%22%2C%0AIF(B3%3D%22Camel_Shell_Urn%22%2C%2212%22%2C%0AIF(B3%3D%22Garden_Rock_Urn%22%2C%2212%22%2C%0AIF(B3%3D%22Paw_Print_Rock_Urn%22%2C%2212%22%2C%0AIF(B3%3D%22Cat_Figurine_Urn%22%2C%2212%22%2C%0AIF(B3%3D%22Camouflage_Urn%22%2C%2212%22%2C%0AIF(B3%3D%22Brown_%26amp%3B_Gold_Cloisonne_Urn%22%2C%2212%22%2C%0AIF(B3%3D%22Silver_Rose_Urn%22%2C%2212%22%2C%0AIF(B3%3D%22Classic_Urn%22%2C%2212%22%2C%0AIF(B3%3D%22Crystal_Keepsake%22%2C%2212%22%2C%0AIF(B3%3D%22Deluxe_Wind_Chimes%22%2C%2212%22%2C%0AIF(B3%3D%22Glass_Oval_Ornament%22%2C%2212%22%2C%0A%0AIF(B3%3D%22Heart_Memento_Urn%22%2C%228%22%2C%0AIF(B3%3D%22Dog_Tag_Necklace%22%2C%228%22%0A%0A))))))))))))))))))))))))))))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
TimothyE
New 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"

))))))))))))))))))))))))))))
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies