Excel Question

PostExcel Question by **Hypes** » Tue Jun 17, 2014 2:43 pm

I have an excel spreadsheet with some cells calculating values. Can I get Excel to display a message if the value drops to 0? If so how?

It's probably something to do with If's and Then's but I'm not sure what...

Cheers

PostRe: Excel Question by **Tomous** » Tue Jun 17, 2014 10:08 pm

If your value is in cell A1, in A2 do:

=IF(A1=0,"ZERO","NOT ZERO")

If A1 is equal to 0, it will display Zero, and if it isn't equal to zero, it will display Not Zero. Change the text to what message you want it to display. If you don't want it to display anything if it isn't equal to zero, do this:

=IF(A1=0,"ZERO","")

PostRe: Excel Question by **Mommy Christmas** » Wed Jun 18, 2014 10:22 pm

Wouldn't conditional formatting provide a more obvious indicator of a drop to zero?

PostRe: Excel Question by **EnragedYogi** » Wed Jun 18, 2014 11:28 pm

Or you can set your formula within an IF statement in the cell itself.

So if in A1 you have = your formula, change this to =If(your formula = 0, "Zero", your formula). Now you get your formula result, and "Zero" if it equals 0. Only thing is if you put a message as the TRUE part, it'll break any formulas that link to that cell for a numeric value. Unless they are IFs or similar that work off the "Zero" result message.... :shifty:

PostRe: Excel Question by **Death's Head** » Sat Jun 21, 2014 10:26 pm

What do you mean by message? If you just want the message to display in a cell, reworking one of the above suggestions will work. If you want a message box to pop up, you'll need a VBA macro.

PostRe: Excel Question by **Hypes** » Mon Jun 23, 2014 10:24 am

Cheers guys - Yeah Death's Head I was trying to get a message box to pop up.

PostRe: Excel Question by **EnragedYogi** » Tue Jun 24, 2014 11:56 pm

If you're working your formula in a single sheet - so A1 is your formula, say =(B1 * C1) - 10, and you're changing values in B1 and C1 to see what gives a zero, this should work. It'll run every time a cell value changes in Sheet1. Right click on sheet1, select "View code" and paste the below in.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    If Worksheets("sheet1").Range("a1") = 0 Then MsgBox "It's Zero!"
End Sub

PostRe: Excel Question by **Hypes** » Thu Jun 26, 2014 2:30 pm

Cheers Yogi. That's what I was looking for.

If the same formula's are repeated for a number of items and appearing in a1,b1,c1 etc. is there a way of including them all in the formula or do I just have to copy it each time changing the cell letter?

PostRe: Excel Question by **Tomous** » Thu Jun 26, 2014 7:00 pm

Hyperion » Thu Jun 26, 2014 2:30 pm wrote:Cheers Yogi. That's what I was looking for.
If the same formula's are repeated for a number of items and appearing in a1,b1,c1 etc. is there a way of including them all in the formula or do I just have to copy it each time changing the cell letter?

Highlight the cell with the formula then click the bottom right corner of the cell, and while holding the button down, drag the formula down the column and it will autofill with your formula, automatically updating the cell references for you.

PostRe: Excel Question by **Hypes** » Fri Jun 27, 2014 10:03 am

Thanks Tomous, that's not what I meant. Sorry if I wasn't clear.

Private Sub Worksheet_Change(ByVal Target As Range)
If Worksheets("sheet1").Range("a1") = 0 Then MsgBox "It's Zero!"
End Sub

If I want it to read data from cell a1 and display a message then this is ok, but I also would like it to do the same for cell a2, a3, a4.....a100
I don't know if that can be worked in to the formula or if it has to just be c&p'd changing the cell number each time.

PostRe: Excel Question by **Tomous** » Fri Jun 27, 2014 12:51 pm

Hyperion » Fri Jun 27, 2014 10:03 am wrote:Thanks Tomous, that's not what I meant. Sorry if I wasn't clear.

Private Sub Worksheet_Change(ByVal Target As Range)
If Worksheets("sheet1").Range("a1") = 0 Then MsgBox "It's Zero!"
End Sub

If I want it to read data from cell a1 and display a message then this is ok, but I also would like it to do the same for cell a2, a3, a4.....a100
I don't know if that can be worked in to the formula or if it has to just be c&p'd changing the cell number each time.

Maybe try changing .Range("a1") to .Range("a1:a100").

I'm not sure though as I haven't used a formula like that before.

PostRe: Excel Question by **EnragedYogi** » Sat Jun 28, 2014 2:51 am

Actual responses to Excel discussion :shock:

Also I think I have Hyperion on my 3DS friends list!

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
        
Dim Cell As Variant

For Each Cell In Range("a1:a10")

    If Cell.Value = 0 And Cell.Value <> "" Then
        MsgBox "one of these is zero!"
    Else: End If
   
Next Cell

End Sub

Adding the And cell.value<> "" stops it giving the message when the cell is empty rather than zero. Every time a cell value changes it loops through the range looking for zeros. You could add an increment or .address to let you know how many/which cells = 0

PostRe: Excel Question by **Hypes** » Mon Jun 30, 2014 2:29 pm

Cheers Yogi, that's it!! :wub:

I'd not been able to get the range to work but now it's fine 8-)

I thought it wasn't working and was just throwing the message up every time until I realised that I already had zeroes in the column :fp:

And yes I am on your 3DS friends list :datass:

Thanks everyone for your help!!

Excel Question

Who is online

Affiliates