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?
pdvsaProject financeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Saqib Husain, SyedEngineerCommented:
What should happen if the value is negative?
0
Rory ArchibaldCommented:
Perhaps simply:

=MIN(IFERROR(VLOOKUP("*Interest exp*",IncomeStmnt!$A$3:$L$56,2,0),0),0)
0
Leo TorresSQL 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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Rory ArchibaldCommented:
Worksheet_Change will not be triggered by a formula calculation though - you would need the Calculate event.
0
Leo TorresSQL 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
pdvsaProject 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
pdvsaProject 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
Saqib Husain, SyedEngineerCommented:
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
Rory ArchibaldCommented:
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
pdvsaProject 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.