Force a cell to be filled based on text in another - data validation possible?

**mg2706** · 03-22-2022, 01:37 AM

I am trying to improve a spreadsheet that is filled out by a range of people auditing customer interactions.

There is a date cell that needs to be filled out if the result of the audit is "Breach" but not if the result is "Compliant".

I want to either force the date cell to be filled if the results is Breach, OR not allow the audit result to be Breach unless there is a date in the remedial action.

I'd like to use data validation if possible, and not have any formulas in the cell itself.

I've used the data validation formula =NOT(ISBLANK(date cell)) in the result column to force the date to be filled out before the result, but can't figure out how to limit that to only certain result types.

An example spreadsheet is attached.

**etaf** · 03-22-2022, 04:27 AM

Ignore my post

**AskMeAboutExcel** · 03-22-2022, 04:39 AM

It's difficult to 'force' without use of VBA, but there are some slightly softer interventions that may help

how about:
- Using Data Validation for Column E, allowing dates. You can restrict it to certain date ranges - e.g. you can put the start date as the date in Column D (e.g. =D2 in data validation for cell E2), and the end date as '=today()'. You can also put a custom Input Message to make it clearer on what the expected inputs are when they click on the cell

and either

- Using conditional formatting in Column H so that it highlights if Audit Result is Breach without a date of Remedial Action has been filled out, or
- Using conditional formatting in Column E so that it highlight if date of Remedial Action is missing when Audit Result is breach.

Have attached a mockup with the two examples of this

Force a cell to be filled based on text in another - data validation possible?

LinkBack

Thread Tools

Rate This Thread

Display

Force a cell to be filled based on text in another - data validation possible?

Re: Force a cell to be filled based on text in another - data validation possible?

Re: Force a cell to be filled based on text in another - data validation possible?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] clear data validation drop down based on text in another cell

[SOLVED] Data validation to force uppercase two letter (and a space) prefix with text string entry

[SOLVED] Data Validation to force Uppercase and From List only Text

Data Cross Reference (validation) based on Text in a Cell

[SOLVED] Trouble using Data Validation/Custom to prevent entries based on text in another cell

[SOLVED] Data validation based on another cell containing specific text

Updating Two Pivot Filters based on Two Data Validation Cell Text

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions