Data Validation Questions.

Hello! I was wondering if there was a data validation formula that could be used below for cells B2:G6 that would only allow whole numbers to be entered, only numbers between 300 and 8000 to be enter...

excel

Formulas and Functions

dmissel23

Copper Contributor

Jan 05, 2022

Thanks mtarler! Do you know of a formula that I could apply to the same range that would only impact blank cells? And if so, is it possible to combine it with the formula you provided me?

If I apply the message box to just the specific red cells in each table, we have some instances where we would need to change a cell from red to grey and enter a value in it and when we do, it sounds like we would have to go back into the cell after a value has been entered and remove the validation from the cell that originally was put in place when it was blank (red).

I was hoping we could assign a formula so that after a value has been entered in a red cell, excel would recognize the value so no message box appears in the future when you click on the cell that had the value added.

Thanks for your help!

mtarler

Silver Contributor

Jan 05, 2022

I do not believe you can detect what the value is before the update using data validation. You can use conditional formatting to automatically highlight blank cells red and then after you enter a value it can automatically clear the highlighting. Another problem is that even if data validation is used you would need it to be 'warning' if it was blank but 'alert' if it is out of bounds which you also can't do. You can do this using VBA if it is so important.

Forum Discussion

Data Validation Questions.

Resources