I tried to get back and reply on my last question but was unable to so I had
to start a new question. I'm trying to use the data validation to make a pop
up come up in a cell (e7) if two criteria are met. One is if B3="General
Expenses" and the other is if e7 is more than $500.

I got a couple of formulas from my previous question. The problem is that as
soon as you click on the cell the message pops up. This happens whether the
cell is blank or over or under the $500 and regardless if B3 says "General
Expenses" or not.
Is there a way to get the pop up only if the other criteria are met? Thanks
again!

I've just created a new workbook and entered the validation in cell E7 as

=IF($B$3="General Expenses",IF(E7500,FALSE,TRUE),TRUE)

This is the same formula I posted earlier. Make sure "Ignore Blank" is
checked. This formula works fine on mine. Are you sure you entered it
correctly (sorry I know that's a basic question but it really does work on
this machine).

The formula displays an error if B3 contains "General Expenses" and you
attempt to enter a value over 500. If you enter a value equal to or less
than 500 the error message does not appear. If however, cell B3 does NOT
contain the phrase "General Expenses", then the error message will not appear
no matter what value you use in cell E7.

"Klee" wrote:

I tried to get back and reply on my last question but was unable to so I had
to start a new question. I'm trying to use the data validation to make a pop
up come up in a cell (e7) if two criteria are met. One is if B3="General
Expenses" and the other is if e7 is more than $500.

I got a couple of formulas from my previous question. The problem is that as
soon as you click on the cell the message pops up. This happens whether the
cell is blank or over or under the $500 and regardless if B3 says "General
Expenses" or not.
Is there a way to get the pop up only if the other criteria are met? Thanks
again!

Yes, I did enter the formula correctly but it's a question that's worth
asking.

I have several different formulas in E8,E9 ect to see what works. Yours is
great except for the error. I want people to be able to enter an amount in
the cell regardless of the amount. I just want the pop up to let them know
that they need to fill out a different form when the amount is over $500.
Sorry that I didn't explain that better earlier.

Any suggestions?


"Dom_Ciccone" wrote:

I've just created a new workbook and entered the validation in cell E7 as

=IF($B$3="General Expenses",IF(E7500,FALSE,TRUE),TRUE)

This is the same formula I posted earlier. Make sure "Ignore Blank" is
checked. This formula works fine on mine. Are you sure you entered it
correctly (sorry I know that's a basic question but it really does work on
this machine).

The formula displays an error if B3 contains "General Expenses" and you
attempt to enter a value over 500. If you enter a value equal to or less
than 500 the error message does not appear. If however, cell B3 does NOT
contain the phrase "General Expenses", then the error message will not appear
no matter what value you use in cell E7.

"Klee" wrote:

I tried to get back and reply on my last question but was unable to so I had
to start a new question. I'm trying to use the data validation to make a pop
up come up in a cell (e7) if two criteria are met. One is if B3="General
Expenses" and the other is if e7 is more than $500.

I got a couple of formulas from my previous question. The problem is that as
soon as you click on the cell the message pops up. This happens whether the
cell is blank or over or under the $500 and regardless if B3 says "General
Expenses" or not.
Is there a way to get the pop up only if the other criteria are met? Thanks
again!

Ahhhh I see. I'm not aware of any way to do this In Excel itself. If you
are comfortable using VBA the probably the best place to do this would be
attached to the Worksheet_Change() event, checking that the Target cell is
within the range (E7:E60) and if performing your checks there. You could
then flag this to the user as a message box or add a comment to the cell and
display that.

"Klee" wrote:

Yes, I did enter the formula correctly but it's a question that's worth
asking.

I have several different formulas in E8,E9 ect to see what works. Yours is
great except for the error. I want people to be able to enter an amount in
the cell regardless of the amount. I just want the pop up to let them know
that they need to fill out a different form when the amount is over $500.
Sorry that I didn't explain that better earlier.

Any suggestions?


"Dom_Ciccone" wrote:

I've just created a new workbook and entered the validation in cell E7 as

=IF($B$3="General Expenses",IF(E7500,FALSE,TRUE),TRUE)

This is the same formula I posted earlier. Make sure "Ignore Blank" is
checked. This formula works fine on mine. Are you sure you entered it
correctly (sorry I know that's a basic question but it really does work on
this machine).

The formula displays an error if B3 contains "General Expenses" and you
attempt to enter a value over 500. If you enter a value equal to or less
than 500 the error message does not appear. If however, cell B3 does NOT
contain the phrase "General Expenses", then the error message will not appear
no matter what value you use in cell E7.

"Klee" wrote:

I tried to get back and reply on my last question but was unable to so I had
to start a new question. I'm trying to use the data validation to make a pop
up come up in a cell (e7) if two criteria are met. One is if B3="General
Expenses" and the other is if e7 is more than $500.

I got a couple of formulas from my previous question. The problem is that as
soon as you click on the cell the message pops up. This happens whether the
cell is blank or over or under the $500 and regardless if B3 says "General
Expenses" or not.
Is there a way to get the pop up only if the other criteria are met? Thanks
again!

I don't know much about VBA. If there is anyone who would be able to supply
me with the text to paste in it would be fantastic.

Thanks for the info though Dom, I would have just kept messing around with
data validation until my head exploded

"Dom_Ciccone" wrote:

Ahhhh I see. I'm not aware of any way to do this In Excel itself. If you
are comfortable using VBA the probably the best place to do this would be
attached to the Worksheet_Change() event, checking that the Target cell is
within the range (E7:E60) and if performing your checks there. You could
then flag this to the user as a message box or add a comment to the cell and
display that.

"Klee" wrote:

Yes, I did enter the formula correctly but it's a question that's worth
asking.

I have several different formulas in E8,E9 ect to see what works. Yours is
great except for the error. I want people to be able to enter an amount in
the cell regardless of the amount. I just want the pop up to let them know
that they need to fill out a different form when the amount is over $500.
Sorry that I didn't explain that better earlier.

Any suggestions?


"Dom_Ciccone" wrote:

I've just created a new workbook and entered the validation in cell E7 as

=IF($B$3="General Expenses",IF(E7500,FALSE,TRUE),TRUE)

This is the same formula I posted earlier. Make sure "Ignore Blank" is
checked. This formula works fine on mine. Are you sure you entered it
correctly (sorry I know that's a basic question but it really does work on
this machine).

The formula displays an error if B3 contains "General Expenses" and you
attempt to enter a value over 500. If you enter a value equal to or less
than 500 the error message does not appear. If however, cell B3 does NOT
contain the phrase "General Expenses", then the error message will not appear
no matter what value you use in cell E7.

"Klee" wrote:

I tried to get back and reply on my last question but was unable to so I had
to start a new question. I'm trying to use the data validation to make a pop
up come up in a cell (e7) if two criteria are met. One is if B3="General
Expenses" and the other is if e7 is more than $500.

I got a couple of formulas from my previous question. The problem is that as
soon as you click on the cell the message pops up. This happens whether the
cell is blank or over or under the $500 and regardless if B3 says "General
Expenses" or not.
Is there a way to get the pop up only if the other criteria are met? Thanks
again!

You can use an Error message, instead of an Input message.
In the data validation dialog box, on the Error Alert tab, create a message.
From the Style dropdown, choose either Information or Warning.
Users will see the message, but will be able to enter any amount.

Klee wrote:
Yes, I did enter the formula correctly but it's a question that's worth
asking.

I have several different formulas in E8,E9 ect to see what works. Yours is
great except for the error. I want people to be able to enter an amount in
the cell regardless of the amount. I just want the pop up to let them know
that they need to fill out a different form when the amount is over $500.
Sorry that I didn't explain that better earlier.

Any suggestions?


"Dom_Ciccone" wrote:


I've just created a new workbook and entered the validation in cell E7 as

=IF($B$3="General Expenses",IF(E7500,FALSE,TRUE),TRUE)

This is the same formula I posted earlier. Make sure "Ignore Blank" is
checked. This formula works fine on mine. Are you sure you entered it
correctly (sorry I know that's a basic question but it really does work on
this machine).

The formula displays an error if B3 contains "General Expenses" and you
attempt to enter a value over 500. If you enter a value equal to or less
than 500 the error message does not appear. If however, cell B3 does NOT
contain the phrase "General Expenses", then the error message will not appear
no matter what value you use in cell E7.

"Klee" wrote:


I tried to get back and reply on my last question but was unable to so I had
to start a new question. I'm trying to use the data validation to make a pop
up come up in a cell (e7) if two criteria are met. One is if B3="General
Expenses" and the other is if e7 is more than $500.

I got a couple of formulas from my previous question. The problem is that as
soon as you click on the cell the message pops up. This happens whether the
cell is blank or over or under the $500 and regardless if B3 says "General
Expenses" or not.
Is there a way to get the pop up only if the other criteria are met? Thanks
again!



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Here is the code you require. It is embellished a little but you can delete
the parts you don't want. I included things purely as an example. Open the
Visual Basic Editor and in the Project Explorer on the left find the workbook
you wish to add the code to. Double click the Sheet name of the sheet that
will contain your data and add this code to it:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim entryrange As Range
Set entryrange = ActiveSheet.Range("E7:E60")

If Not Application.Intersect(Target, entryrange) Is Nothing Then
If ActiveSheet.Range("B3").Value = "General Expenses" Then
If Target.Value 500 Then

'This line adds a comment to the cell - delete if you wish
If Target.Comment Is Nothing Then
Target.AddComment ("Remember to complete form <NAME")
End If

'This line displays a message box - delete if you wish
MsgBox ("Remember to complete form <NAME")

'This line changes the cell colour - delete if you wish
Target.Interior.ColorIndex = 3
Else
If Not Target.Comment Is Nothing Then
Target.Comment.Delete
Target.Interior.ColorIndex = 0
End If
End If
End If
End If
End Sub


This does three things (slight overkill). It places a comment in the cell,
paints the cell a different colour and displays a message box as well. If
you go back and change the value in the cell to something lower than $500 it
will remove the comment and remove the fill colour from the cell.

Hope this helps you.

"Klee" wrote:

I don't know much about VBA. If there is anyone who would be able to supply
me with the text to paste in it would be fantastic.

Thanks for the info though Dom, I would have just kept messing around with
data validation until my head exploded

"Dom_Ciccone" wrote:

Ahhhh I see. I'm not aware of any way to do this In Excel itself. If you
are comfortable using VBA the probably the best place to do this would be
attached to the Worksheet_Change() event, checking that the Target cell is
within the range (E7:E60) and if performing your checks there. You could
then flag this to the user as a message box or add a comment to the cell and
display that.

"Klee" wrote:

Yes, I did enter the formula correctly but it's a question that's worth
asking.

I have several different formulas in E8,E9 ect to see what works. Yours is
great except for the error. I want people to be able to enter an amount in
the cell regardless of the amount. I just want the pop up to let them know
that they need to fill out a different form when the amount is over $500.
Sorry that I didn't explain that better earlier.

Any suggestions?


"Dom_Ciccone" wrote:

I've just created a new workbook and entered the validation in cell E7 as

=IF($B$3="General Expenses",IF(E7500,FALSE,TRUE),TRUE)

This is the same formula I posted earlier. Make sure "Ignore Blank" is
checked. This formula works fine on mine. Are you sure you entered it
correctly (sorry I know that's a basic question but it really does work on
this machine).

The formula displays an error if B3 contains "General Expenses" and you
attempt to enter a value over 500. If you enter a value equal to or less
than 500 the error message does not appear. If however, cell B3 does NOT
contain the phrase "General Expenses", then the error message will not appear
no matter what value you use in cell E7.

"Klee" wrote:

I tried to get back and reply on my last question but was unable to so I had
to start a new question. I'm trying to use the data validation to make a pop
up come up in a cell (e7) if two criteria are met. One is if B3="General
Expenses" and the other is if e7 is more than $500.

I got a couple of formulas from my previous question. The problem is that as
soon as you click on the cell the message pops up. This happens whether the
cell is blank or over or under the $500 and regardless if B3 says "General
Expenses" or not.
Is there a way to get the pop up only if the other criteria are met? Thanks
again!

Hi Deb

I'm ashamed to say that in 12 years of using Excel, I have never noticed
that Style dropdown.

--
Regards

Roger Govier


"Debra Dalgleish" wrote in message
...
You can use an Error message, instead of an Input message.
In the data validation dialog box, on the Error Alert tab, create a
message.
From the Style dropdown, choose either Information or Warning.
Users will see the message, but will be able to enter any amount.

Klee wrote:
Yes, I did enter the formula correctly but it's a question that's
worth asking. I have several different formulas in E8,E9 ect to see
what works. Yours is great except for the error. I want people to be
able to enter an amount in the cell regardless of the amount. I just
want the pop up to let them know that they need to fill out a
different form when the amount is over $500. Sorry that I didn't
explain that better earlier.

Any suggestions?


"Dom_Ciccone" wrote:


I've just created a new workbook and entered the validation in cell
E7 as

=IF($B$3="General Expenses",IF(E7500,FALSE,TRUE),TRUE)

This is the same formula I posted earlier. Make sure "Ignore Blank"
is checked. This formula works fine on mine. Are you sure you
entered it correctly (sorry I know that's a basic question but it
really does work on this machine).

The formula displays an error if B3 contains "General Expenses" and
you attempt to enter a value over 500. If you enter a value equal to
or less than 500 the error message does not appear. If however, cell
B3 does NOT contain the phrase "General Expenses", then the error
message will not appear no matter what value you use in cell E7.

"Klee" wrote:


I tried to get back and reply on my last question but was unable to
so I had to start a new question. I'm trying to use the data
validation to make a pop up come up in a cell (e7) if two criteria
are met. One is if B3="General Expenses" and the other is if e7 is
more than $500.

I got a couple of formulas from my previous question. The problem is
that as soon as you click on the cell the message pops up. This
happens whether the cell is blank or over or under the $500 and
regardless if B3 says "General Expenses" or not.
Is there a way to get the pop up only if the other criteria are met?
Thanks again!



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

No matter how much you know, there's always something new to discover!

Roger Govier wrote:
Hi Deb

I'm ashamed to say that in 12 years of using Excel, I have never noticed
that Style dropdown.



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Debra, Thank you so much. his is exactly what I needed.

Thanks to all for the help.

"Debra Dalgleish" wrote:

You can use an Error message, instead of an Input message.
In the data validation dialog box, on the Error Alert tab, create a message.
From the Style dropdown, choose either Information or Warning.
Users will see the message, but will be able to enter any amount.

Klee wrote:
Yes, I did enter the formula correctly but it's a question that's worth
asking.

I have several different formulas in E8,E9 ect to see what works. Yours is
great except for the error. I want people to be able to enter an amount in
the cell regardless of the amount. I just want the pop up to let them know
that they need to fill out a different form when the amount is over $500.
Sorry that I didn't explain that better earlier.

Any suggestions?


"Dom_Ciccone" wrote:


I've just created a new workbook and entered the validation in cell E7 as

=IF($B$3="General Expenses",IF(E7500,FALSE,TRUE),TRUE)

This is the same formula I posted earlier. Make sure "Ignore Blank" is
checked. This formula works fine on mine. Are you sure you entered it
correctly (sorry I know that's a basic question but it really does work on
this machine).

The formula displays an error if B3 contains "General Expenses" and you
attempt to enter a value over 500. If you enter a value equal to or less
than 500 the error message does not appear. If however, cell B3 does NOT
contain the phrase "General Expenses", then the error message will not appear
no matter what value you use in cell E7.

"Klee" wrote:


I tried to get back and reply on my last question but was unable to so I had
to start a new question. I'm trying to use the data validation to make a pop
up come up in a cell (e7) if two criteria are met. One is if B3="General
Expenses" and the other is if e7 is more than $500.

I got a couple of formulas from my previous question. The problem is that as
soon as you click on the cell the message pops up. This happens whether the
cell is blank or over or under the $500 and regardless if B3 says "General
Expenses" or not.
Is there a way to get the pop up only if the other criteria are met? Thanks
again!



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

You're welcome, and thanks for letting me know. There's a bit more
information on data validation messages he

http://www.contextures.com/xlDataVal04.html

Klee wrote:
Debra, Thank you so much. his is exactly what I needed.

Thanks to all for the help.

"Debra Dalgleish" wrote:


You can use an Error message, instead of an Input message.
In the data validation dialog box, on the Error Alert tab, create a message.
From the Style dropdown, choose either Information or Warning.
Users will see the message, but will be able to enter any amount.

Klee wrote:

Yes, I did enter the formula correctly but it's a question that's worth
asking.

I have several different formulas in E8,E9 ect to see what works. Yours is
great except for the error. I want people to be able to enter an amount in
the cell regardless of the amount. I just want the pop up to let them know
that they need to fill out a different form when the amount is over $500.
Sorry that I didn't explain that better earlier.

Any suggestions?


"Dom_Ciccone" wrote:



I've just created a new workbook and entered the validation in cell E7 as

=IF($B$3="General Expenses",IF(E7500,FALSE,TRUE),TRUE)

This is the same formula I posted earlier. Make sure "Ignore Blank" is
checked. This formula works fine on mine. Are you sure you entered it
correctly (sorry I know that's a basic question but it really does work on
this machine).

The formula displays an error if B3 contains "General Expenses" and you
attempt to enter a value over 500. If you enter a value equal to or less
than 500 the error message does not appear. If however, cell B3 does NOT
contain the phrase "General Expenses", then the error message will not appear
no matter what value you use in cell E7.

"Klee" wrote:



I tried to get back and reply on my last question but was unable to so I had
to start a new question. I'm trying to use the data validation to make a pop
up come up in a cell (e7) if two criteria are met. One is if B3="General
Expenses" and the other is if e7 is more than $500.

I got a couple of formulas from my previous question. The problem is that as
soon as you click on the cell the message pops up. This happens whether the
cell is blank or over or under the $500 and regardless if B3 says "General
Expenses" or not.
Is there a way to get the pop up only if the other criteria are met? Thanks
again!


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Hi Dom,
I just saw this and tried it. It worked perfectly. Thanks very much for all
your time. I really appreciate it. I need to find a book to learn how to do
this. It's very interesting.
Thanks again,

"Dom_Ciccone" wrote:

Here is the code you require. It is embellished a little but you can delete
the parts you don't want. I included things purely as an example. Open the
Visual Basic Editor and in the Project Explorer on the left find the workbook
you wish to add the code to. Double click the Sheet name of the sheet that
will contain your data and add this code to it:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim entryrange As Range
Set entryrange = ActiveSheet.Range("E7:E60")

If Not Application.Intersect(Target, entryrange) Is Nothing Then
If ActiveSheet.Range("B3").Value = "General Expenses" Then
If Target.Value 500 Then

'This line adds a comment to the cell - delete if you wish
If Target.Comment Is Nothing Then
Target.AddComment ("Remember to complete form <NAME")
End If

'This line displays a message box - delete if you wish
MsgBox ("Remember to complete form <NAME")

'This line changes the cell colour - delete if you wish
Target.Interior.ColorIndex = 3
Else
If Not Target.Comment Is Nothing Then
Target.Comment.Delete
Target.Interior.ColorIndex = 0
End If
End If
End If
End If
End Sub


This does three things (slight overkill). It places a comment in the cell,
paints the cell a different colour and displays a message box as well. If
you go back and change the value in the cell to something lower than $500 it
will remove the comment and remove the fill colour from the cell.

Hope this helps you.

"Klee" wrote:

I don't know much about VBA. If there is anyone who would be able to supply
me with the text to paste in it would be fantastic.

Thanks for the info though Dom, I would have just kept messing around with
data validation until my head exploded

"Dom_Ciccone" wrote:

Ahhhh I see. I'm not aware of any way to do this In Excel itself. If you
are comfortable using VBA the probably the best place to do this would be
attached to the Worksheet_Change() event, checking that the Target cell is
within the range (E7:E60) and if performing your checks there. You could
then flag this to the user as a message box or add a comment to the cell and
display that.

"Klee" wrote:

Yes, I did enter the formula correctly but it's a question that's worth
asking.

I have several different formulas in E8,E9 ect to see what works. Yours is
great except for the error. I want people to be able to enter an amount in
the cell regardless of the amount. I just want the pop up to let them know
that they need to fill out a different form when the amount is over $500.
Sorry that I didn't explain that better earlier.

Any suggestions?


"Dom_Ciccone" wrote:

I've just created a new workbook and entered the validation in cell E7 as

=IF($B$3="General Expenses",IF(E7500,FALSE,TRUE),TRUE)

This is the same formula I posted earlier. Make sure "Ignore Blank" is
checked. This formula works fine on mine. Are you sure you entered it
correctly (sorry I know that's a basic question but it really does work on
this machine).

The formula displays an error if B3 contains "General Expenses" and you
attempt to enter a value over 500. If you enter a value equal to or less
than 500 the error message does not appear. If however, cell B3 does NOT
contain the phrase "General Expenses", then the error message will not appear
no matter what value you use in cell E7.

"Klee" wrote:

I tried to get back and reply on my last question but was unable to so I had
to start a new question. I'm trying to use the data validation to make a pop
up come up in a cell (e7) if two criteria are met. One is if B3="General
Expenses" and the other is if e7 is more than $500.

I got a couple of formulas from my previous question. The problem is that as
soon as you click on the cell the message pops up. This happens whether the
cell is blank or over or under the $500 and regardless if B3 says "General
Expenses" or not.
Is there a way to get the pop up only if the other criteria are met? Thanks
again!

lol Me either. Thanks Debra for giving me a much easier way of doing it!

"Roger Govier" wrote:

Hi Deb

I'm ashamed to say that in 12 years of using Excel, I have never noticed
that Style dropdown.

--
Regards

Roger Govier


"Debra Dalgleish" wrote in message
...
You can use an Error message, instead of an Input message.
In the data validation dialog box, on the Error Alert tab, create a
message.
From the Style dropdown, choose either Information or Warning.
Users will see the message, but will be able to enter any amount.

Klee wrote:
Yes, I did enter the formula correctly but it's a question that's
worth asking. I have several different formulas in E8,E9 ect to see
what works. Yours is great except for the error. I want people to be
able to enter an amount in the cell regardless of the amount. I just
want the pop up to let them know that they need to fill out a
different form when the amount is over $500. Sorry that I didn't
explain that better earlier.

Any suggestions?


"Dom_Ciccone" wrote:


I've just created a new workbook and entered the validation in cell
E7 as

=IF($B$3="General Expenses",IF(E7500,FALSE,TRUE),TRUE)

This is the same formula I posted earlier. Make sure "Ignore Blank"
is checked. This formula works fine on mine. Are you sure you
entered it correctly (sorry I know that's a basic question but it
really does work on this machine).

The formula displays an error if B3 contains "General Expenses" and
you attempt to enter a value over 500. If you enter a value equal to
or less than 500 the error message does not appear. If however, cell
B3 does NOT contain the phrase "General Expenses", then the error
message will not appear no matter what value you use in cell E7.

"Klee" wrote:


I tried to get back and reply on my last question but was unable to
so I had to start a new question. I'm trying to use the data
validation to make a pop up come up in a cell (e7) if two criteria
are met. One is if B3="General Expenses" and the other is if e7 is
more than $500.

I got a couple of formulas from my previous question. The problem is
that as soon as you click on the cell the message pops up. This
happens whether the cell is blank or over or under the $500 and
regardless if B3 says "General Expenses" or not.
Is there a way to get the pop up only if the other criteria are met?
Thanks again!



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html