Excel Formula Assistance 1

[ladyck3]

Ok here's the scenario, I'm working on this but having difficulty, I'm sure the isblank function is in here but well here it goes.

This is in regards to E2

IF A2 is blank, then put ZERO in E2 however
if A2=>1 then do nothing, or do not change the information in Cell E2

Thanks, y'all are real fast but maybe I'll get it before you

Appreciate the help in advance...

Ladyck3

 

[Crimper]

Try this:

=IF(B2="",0,B2)

I have left the calc returning whatever the value is in B2,
but you can replace this with your calc....

JP

 

[KenWright]

Assuming your existing formula in E2 was say =SUM(I7:I15), then in E2 make it this instead.

=IF(A2="",0,SUM(I7:I15))

eg

=IF(A2="",0,Existing_Formula_in_E2)

What if A2=0 though, as you haven't covered that option

Being 0 is not the same as being blank, but blank does have the same value as 0.

Perhaps you meant:-

=IF(A2=0,0,SUM(I7:I15)) which will actually cover both events, so that if A2 is blank or 0, then you get 0 in E2.

Regards
Ken................


----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[bluedragon2]

And to cover both:

=IF(AND(ISBLANK(A2),A2=0),0,Your Formula in E2)



Blue

If I wasn't Blue, I would just be a Dragon...

 

[ladyck3]

Actually this works too (JP I appreciate your response)

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

However, what we have is a value in A2 and in E2, the user wants to have these changes to change the value of E2, which would require code....

So that answers that

 

[ladyck3]

Well this is fantastic except for one thing... the data in E2 is not a formula, its just data typed in so it would not be a matter of teling it to replace the data with the formula if there are no changes, it would be a matter of telling it to not touch the data if the criteria is met.

That would take some sort of coding right? If so, its something I would not provide because I don't know VBA .. Ken, Blue.. you help me all the time, but I have less of a master at formulas much less VBA too ... you KNOW that by NOW

 

[Bowers74]

Just because I want to be on your list too! ;-)

Paste the following code into the sheet object module for the sheet you want this "function" to be in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
    If IsEmpty(Target) Then
        [E2].Value = 0
    ElseIf Target = 0 Then
        [E2].Value = 0
    Else
        [E2].Value = [E2]
    End If
End If
End Sub

To find the sheet Object module:

1. Press Alt+F11
2. Find your sheet in the TreeView list on the left
3. Double click the sheet object
4. Paste the above code

I hope this helps! And, I hope it qualifies me!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[ladyck3]

LOL.... OKie... Bowers(Mike) when next I require help with VBA, YOU are the one on the list

I appreciate the job ... well done... but since I don't provide VBA Scripting to anyone because I have no clue how to write/debug it... I would not offer this to the user.

HOWEVER.. I have a HUGE little file with all my tips 'n tricks learned here and elsewhere and I promise you, this one is going in my bag of tips

Thanks... consider yourself listed....

(BUT.. you have a LONG way to go to catch up to Ken, Blue and Skip.... you can do it!) hehe

Laurie

 

[DaleWatson123321]

Laurie,

Here's a (non VBA) option to consider...

Can you use another cell (somewhere on the same row perhaps) where the user would enter the value currently being entered into cell E2 ? (Perhaps you could insert a column ?)

If this is possible (for example using D2), then the following formula will work in cell E2...

=IF(A2>=1,D2,0)

I hope this helps. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[ladyck3]

Dale,

Thanks for the tip...

The user just wanted an easy way to just do this and if they have to do work like you are describing, its just not 'magic button' enough. some users just kill me...

So the late post is appreciated... I am most certainly calling this worth the save and for your efforts and the fact that it works and I'm gonna horde it..

One Star for you

Thanks so much!! Its good to see you again, I've not seen a lot of postings from you lately... probably doing real work, huh?

Have a great weekend...



LadyCK3
(aka Laurie)
<fancy Signature Here>
(Just waiting til I think of something catchy, but I wanted a signatue TOO)