Creating a popup box if certain cells remain empty

[andrewb90]

Hello all,

Is it possible to add some conditional formatting to a group of cells to have a popup box appear with a message in it if a certain cell (on a different sheet) does not have any data entered into it? Additionally can I make a popup appear if the numerical value of a cell exceeds or falls below a certain amount?

Thanks,

Andrew

 

[mikerickson]

You could use Data Validation.

 

[andrewb90]

What is a data validation?

 

[mikerickson]

Excel Help will explain how to generate messages if certain cells don't meet the conditions that you specify.

One thing it might not mention is that if you are going to be using cells in different sheets for your Validation, those cells have to be Named Ranges.

 

[andrewb90]

I notice that on the settings option in Data validation there is a spot to input a custom formula. Cell 'D1' needs to be a value of 1-12 and if it is less than 1 or more than 12 or nothing at all, then when any of the cells A2:D10 on the Monday sheet are clicked on a popup needs to appear.
When I enter in that info the popup appears in the cell if the data is wrong, which won't help if the cell gets looked over and nothing is ever entered.

- Andrew

 

[Sharpefiction]

I notice that on the settings option in Data validation there is a spot to input a custom formula. Cell 'D1' needs to be a value of 1-12 and if it is less than 1 or more than 12 or nothing at all, then when any of the cells A2:D10 on the Monday sheet are clicked on a popup needs to appear.
When I enter in that info the popup appears in the cell if the data is wrong, which won't help if the cell gets looked over and nothing is ever entered.

- Andrew

I always use the spreadsheet to test formulas before popping them into the 'validation' of a cell as I can see what is working right away - just a tip.

For example:

Data validation in cell A2 of Monday

=IF(SheetName!D1="",FALSE,IF(OR(SheetName!SheetName!D1<1,SheetName!D1>12),FALSE,TRUE))

Select Data Validation, Custom, uncheck 'ignore blanks' and type in your formula and it should do the trick.

*SheetName will need to be the name of the sheet that D1 is on

 

[andrewb90]

So I changed all of the sheet names to Welcome and I tried to apply it to a cell on my monday sheet and I came up with an error. What am I doing wrong?

Thanks,

Andrew

 

[Sharpefiction]

So I changed all of the sheet names to Welcome and I tried to apply it to a cell on my monday sheet and I came up with an error. What am I doing wrong?

Thanks,

Andrew

Not sure, that sounds odd as it worked for me here... So your formula now looks like this?

=IF(Welcome!D1="",FALSE,IF(OR(Welcome!SheetName!D1<1,Welcome!D1>12),FALSE,TRUE))

Did you remove the ! at the end of SheetName? For example your code reads =IF(WelcomeD1..?

 

[andrewb90]

Yea, I am in the custom setting with ignore blanks unchecked. Am I supposed to be doing this to a specific cell or can it be any cell?

 

[Sharpefiction]

Yea, I am in the custom setting with ignore blanks unchecked. Am I supposed to be doing this to a specific cell or can it be any cell?

It can be any cell.. But for the mo try in just one cell, say A1 and add the validation in as the below, with ignore blanks unchecked
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
=IF(Welcome!D1="",FALSE,IF(OR(Welcome!D1<1,Welcome!D1>12),FALSE,TRUE))<o></o>
<o> </o>
This should then work. If D1 is blank, 0 or 13+ it should come up with a prompt