# Nested if statements , with the ,IF(ISNUMBER(SEARCH

1. ## Nested if statements , with the ,IF(ISNUMBER(SEARCH

Hi every body, first time in here and I wonder if someone could help me with this formula.

I am trying a formula that should looks if in column E (percentage of completion) if in E6 in is between 1 and 99 write a 75, if value is =100 write a 50, if value is 0 wire a 1 BUT if in this row , G6 (comments column) the word "pending appears should a write a o.

This part of the formulas works fine: =IF(AND(E6<100%,E6>0),75,IF(E6=100%,50,IF(E6<1%,1,)))

This part also works fine:=IF(ISNUMBER(SEARCH("pending",G6)),0,"")

BUT all tougher does not work...

=IF(AND(E6<100%,E6>0),75,IF(E6=100%,50,IF(E6<1%,1,IF(ISNUMBER(SEARCH("pending",G6)),0,""))))

Could any body help??

srvgonzalez

2. ## Re: Nested if statements , with the ,IF(ISNUMBER(SEARCH

Hi
I have tested your formula and it works prfectly well for me. So why do you feel it is not working for you?
Tony

3. ## Re: Nested if statements , with the ,IF(ISNUMBER(SEARCH

The problem is that it works bu if the cell C6 has the word "pending" is should write 0, but it doesn't, it takes the results form the others if formulas

4. ## Re: Nested if statements , with the ,IF(ISNUMBER(SEARCH

The problem is that it works bu if the cell C6 has the word "pending" is should write 0, but it doesn't, it takes the results form the others if formulas

5. ## Re: Nested if statements , with the ,IF(ISNUMBER(SEARCH

Try putting the ISNUMBER(SEARCH at the beginning of the formula:

=IF(ISNUMBER(SEARCH("pending",G6)),0,IF(AND(E6<100%,E6>0),75,IF(E6=100%,50,IF(E6<1%,1,""))))

6. ## Re: Nested if statements , with the ,IF(ISNUMBER(SEARCH

Dear Tony ...It works...... Great....

THANK YOU .....
THANK YOU VERY MUCH…IT HAS BEEN A GREAT HELP…

7. ## Re: Nested if statements , with the ,IF(ISNUMBER(SEARCH

You're welcome. Thanks for the feedback!

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

8. ## Re: Nested if statements , with the ,IF(ISNUMBER(SEARCH

Thanks for the info , I just did

9. ## keep track of cell changes as comments

Dear all,

I have this VBA code which I found online to keep track of cell changes as comments in the cells, it works perfectly, BUT I would need to it to works only to certain columns and tot to the worksheet…
Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Variant, y As Variant

If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
x = Target.Value
Application.Undo
y = Target.Value
Target.Value = x
Application.EnableEvents = True

Target.NoteText Text:=Target.NoteText & Chr(10) & "Old value " & y _
& " changed to " & x & " on " & Format(Date, "mm-dd-yyyy") _
& " by " & Environ("username")

Target.Comment.Shape.AutoShapeType = msoShapeRoundedRectangle
Target.Comment.Shape.TextFrame.AutoSize = True
End Sub

Does someone know how to change it to target only, for example Colum S, T and W??
Thanks a lot..

There are currently 1 users browsing this thread. (0 members and 1 guests)