+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Registered User
    Join Date
    05-19-2014
    MS-Off Ver
    Excel 2003
    Posts
    12

    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??

    Thank you in advance
    srvgonzalez

  2. #2
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    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. #3
    Registered User
    Join Date
    05-19-2014
    MS-Off Ver
    Excel 2003
    Posts
    12

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

    Hi Tony..Thanks for your replay..

    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. #4
    Registered User
    Join Date
    05-19-2014
    MS-Off Ver
    Excel 2003
    Posts
    12

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

    Hi Tony..Thanks for your replay..

    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. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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,""))))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Registered User
    Join Date
    05-19-2014
    MS-Off Ver
    Excel 2003
    Posts
    12

    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. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

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

    You're welcome. Thanks for the feedback!


    If your question has been solved please mark the thread as being solved.

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

  8. #8
    Registered User
    Join Date
    05-19-2014
    MS-Off Ver
    Excel 2003
    Posts
    12

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

    Thanks for the info , I just did

  9. #9
    Registered User
    Join Date
    05-19-2014
    MS-Off Ver
    Excel 2003
    Posts
    12

    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..

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. 7 nested IF statements with a SEARCH added IF(ISNUMBER(SEARCH({"INSUR","AP REFUND"}
    By michaelproctor001 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 12-10-2013, 02:13 PM
  2. [SOLVED] simple search function using nested IF and Vlookup statements
    By cherryt in forum Excel Formulas & Functions
    Replies: 21
    Last Post: 08-27-2013, 08:45 AM
  3. Nested IF statement with multiple ISNUMBER(SEARCH) formula
    By mlester in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-08-2013, 12:40 PM
  4. Isnumber nested with sumproduct
    By dastgir in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-02-2012, 07:28 AM
  5. Nested IF functions with Search statements
    By swpowers27 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-12-2005, 04:28 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1