Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Limit the Value of a Cell to only Negative

Posted on 2012-03-28
Medium Priority
286 Views
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?
0
Question by:pdvsa
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 3
• 3
• 2
• +1

LVL 43

Expert Comment

ID: 37776688
What should happen if the value is negative?
0

LVL 85

Expert Comment

ID: 37777085
Perhaps simply:

=MIN(IFERROR(VLOOKUP("*Interest exp*",IncomeStmnt!\$A\$3:\$L\$56,2,0),0),0)
0

LVL 8

Accepted Solution

Leo Torres earned 800 total points
ID: 37777238
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

LVL 85

Expert Comment

ID: 37777288
Worksheet_Change will not be triggered by a formula calculation though - you would need the Calculate event.
0

LVL 8

Expert Comment

ID: 37777313
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

Author Comment

ID: 37781168
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

Author Comment

ID: 37781428
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

LVL 43

Expert Comment

ID: 37781434
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

LVL 85

Assisted Solution

Rory Archibald earned 1200 total points
ID: 37781442
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

Author Closing Comment

ID: 37781717
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are â€¦
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will diâ€¦
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to usâ€¦
###### Suggested Courses
Course of the Month6 days, left to enroll

#### 688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.