Solved

Limit the Value of a Cell to only Negative

Posted on 2012-03-28
10
280 Views
Last Modified: 2012-03-29
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
Comment
Question by:pdvsa
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37776688
What should happen if the value is negative?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37777085
Perhaps simply:

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

Accepted Solution

by:
Leo Torres earned 200 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

Open in new window

0
 
LVL 85

Expert Comment

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

Expert Comment

by:Leo Torres
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:pdvsa
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

by:pdvsa
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

by:Saqib Husain, Syed
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

by:Rory Archibald
Rory Archibald earned 300 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

by:pdvsa
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now