Warning Messages Conditional on Cell Values

[pecosvil]

Hi everybody. I have a spreadsheet where people add information, and other spreadsheets that do calculations to check that the information entered makes sense (for instance, that the quotient between two values is less than X).

I would like to refine the process, so that automatically prompts a confirmation when the program suspects the information doesn't make too much sense.

I'll give you an example.
Column A has purchase dates
Column B has return dates
I already conditionally formatted the cells in order to fill them in red if "B is less than A", but I would also like to have a message prompted saying "Are you sure that B is less than A?"

I would also like to have similar messages for more complex conditioning (for instance, if "B is less than A" & "C is different from USA" & "D equals F")

I have some experience in Stata programming, but not in VBA (I just limited myself to copy and paste codes from the internet, but I think I will be able to manipulate the conditions as long as I know where they are).

Many thanks in advance!

 

[rdw72777]

Okay I see it now, must be my comp

 

[rdw72777]

Try Data Validation Lists.

For instance, if your Purchase Date is in Cell A1 and your return date is in cell B1 then do the following for cell B1:

(Steps Assume you use Excel 2007)

1. Select Cell B1
2. Go to the Data toolbar
3. Select Data Validation
4. For "Settings" tab, select "Custom"
5. In the formula Bar, type the formula "=B1>A1"
6. On the "Error Alert" tab, select Warning (Allows entry but shows a message box when condition isn't met)
7. For Title, type "Error"
8. For Error Message, type "Return Date cannot be before Purchase Date"

Should do exactly what you want...no code!!!! Same type of logic can be applied to do your advanced messaging, just need to update the formula in step 5 with some AND or maybe OR functions and you'll be on your way.

 

[pecosvil]

Thanks Ryan!
But I actually need it to be a "Confirmation Message", so that the user has to review the data once again.
The example I gave was too simplified, but -following the example provided- it my be the case that the "Return Date" is indeed before the "Purchase Date" (I am working with Bonds, and some investors have in their portfolios bonds that have matured -for instance, if the bond is in default-)

 

[rdw72777]

What do you mean by "confirmation message"? In other words, what about the solution I provided doesn't meet your needs?

 

[pecosvil]

I'm sorry, i didn't do it right. Yes, it works perfectly for the current case. Thanks!
One more question, somehow related. I want to condition the format (and also the data validation) on information on other worksheet, but conditional format returns error when references are in another worksheet. Basically, the condition would be something like this:

Format Sheet1!A1 if Sheet1!A1/Sheet2!A1>2
Format Sheet1!A2 if Sheet1!A2/Sheet2!A2>2
...and so on.

Many thanks!!

 

[rdw72777]

I'm not sure if this is something you can workaround easily. Is it possible to bring the value from the second sheet into the first sheet into a hidden row/column as a "dirty" workaround?

 

[pecosvil]

I'm not sure if this is something you can workaround easily. Is it possible to bring the value from the second sheet into the first sheet into a hidden row/column as a "dirty" workaround?

Yes, actually that's what I did after several tries with INDIRECT function (in some forum it said that it could work...).

Thanks for your help!