# Limit the Value of a Cell to only Negative

Hello,

I would like to limit the value of a cell to only be negative.
I have a formula in this cell
=IFERROR(VLOOKUP("*Interest exp*",IncomeStmnt!\$A\$3:\$L\$56,2,0),0)

Therefore it is not a manuel entry.
I know I can limit to only negative if I actually typed a number into the cell but this is not the case.

How can I limit the value of the cell to only negative if there is a formula in the cell?
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EngineerCommented:
What should happen if the value is negative?
0
Commented:
Perhaps simply:

=MIN(IFERROR(VLOOKUP("*Interest exp*",IncomeStmnt!\$A\$3:\$L\$56,2,0),0),0)
0
SQL DeveloperCommented:
The question is so what do you want to happen if the value is not negative.. Error message to user..

this would have to be done in VBA code..

``````Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A1") Then
If Range("A1").Value < 0 Then

MsgBox ("Value must be positive")
End If
End If
End Sub
``````
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
Worksheet_Change will not be triggered by a formula calculation though - you would need the Calculate event.
0
SQL DeveloperCommented:
Good point rorya:

I im out of shape on VBA its been a while..

Thanks.. but the logic remains the same it should still work

:)
0
Project financeAuthor Commented:
Sorry for my late reply.  I have been very busy with my "real" job.  I hope to test in a few hours when i am at a computer.   Thank you
0
Project financeAuthor Commented:
Hi, I would rather not have a VBA enabled excel file (if possible).  A warning message is not absolutely necessary but it nice to have but I think the only way to have that is by VBA but what do I know (not much).  If the file is distributed, otheres will not know what to do when they get the enable macros security message.

I am not sure if the formula by rorya changes the value to negative in cell:  (VLOOKUP("*Interest exp*",IncomeStmnt!\$A\$3:\$L\$56,2   if it was positive?

thank you.
0
EngineerCommented:
If you simply want the positive value of that number then you can use

=abs(IFERROR(VLOOKUP("*Interest exp*",IncomeStmnt!\$A\$3:\$L\$56,2,0),0))
0
Commented:
No, mine returns 0 if the value is positive. If you simply want to convert it to negative, then:

=-ABS(IFERROR(VLOOKUP("*Interest exp*",IncomeStmnt!\$A\$3:\$L\$56,2,0),0))
0
Project financeAuthor Commented:
I decided to award points to two experts.  I hope that is fair.   I did not say in my initial question I was not interested in VBA.   I thought of this afterwards.  Thank you.

Rorya:  that worked perfectly!
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.