Data Validation in excel

%3CLINGO-SUB%20id%3D%22lingo-sub-335007%22%20slang%3D%22en-US%22%3EData%20Validation%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-335007%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20wonder%20if%20anyone%20can%20help%20me.%3C%2FP%3E%3CP%3EI%20am%20creating%20a%20spreadsheet%20for%20data%20entry%20and%20as%20this%20will%20be%20used%20by%20multiple%20persons%20l%20am%20restricting%20from%20of%20the%20fields%20by%20using%20data%20Validation%20lists.%3C%2FP%3E%3CP%3EWhat%20l%20am%20now%20asking%20for%20assistance%20with%20is%20what%20if%20scenarios%20for%20example%3A%3C%2FP%3E%3CP%3E1.%20In%20column%20a%26nbsp%3Bdata%20validation%20list%20exists%20with%20the%20options%20Yes%20and%20No.%3C%2FP%3E%3CP%3E2.%20n%20the%20next%20column%2C%20If%20option%20Yes%20is%20selected%20then%20his%20prefills%20with%20N%2FA%2C%20but%20at%20the%20same%20time%20if%20No%20is%20selected%20then%20the%20user%20can%20enter%20data%20(in%20free%20text).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20help%20me%3F%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETina%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-335007%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-338590%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-338590%22%20slang%3D%22en-US%22%3ETina%2C%20You%20could%20use%20this%20formula%2C%20%3DIF(B2%3D%22Yes%22%2C%22N%2FA%22%2C%22Enter%20Data%20Here%22)%20it%20would%20mean%20the%20user%20overwrites%20the%20formula.%20If%20you%20didn't%20want%20that%20then%20you%20would%20need%20to%20write%20a%20macro%20to%20enter%20the%20N%2FA%20pre-fill.%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hi,

I wonder if anyone can help me.

I am creating a spreadsheet for data entry and as this will be used by multiple persons l am restricting from of the fields by using data Validation lists.

What l am now asking for assistance with is what if scenarios for example:

1. In column a data validation list exists with the options Yes and No.

2. n the next column, If option Yes is selected then his prefills with N/A, but at the same time if No is selected then the user can enter data (in free text).

 

Can anyone help me?

Thanks

Tina

 

1 Reply
Highlighted
Tina, You could use this formula, =IF(B2="Yes","N/A","Enter Data Here") it would mean the user overwrites the formula. If you didn't want that then you would need to write a macro to enter the N/A pre-fill.