Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: IF Stmt

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I need to change the value of a cell if a criteria is met otherwise leave the current value of that cell intact - How do I do that?

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You could try some VB.

    If Activecell.value = (your criteria) then
    activecell.value = (new value)
    else
    activecell.offsett(1,0).select 'go to next cell
    end if

  3. #3
    New Member
    Join Date
    May 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    you can also use a formula for this.
    =if(celllocation=criteria, newvalue,celllocation)
    substitute the cell address, criteria and newvalue for the names above.

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Phiore,
    When I use your formula I get a 'circular reference' error. This is due to trying to leave the value of the
    'test' cell when the 'criteria' is not met. Any other ideas
    PS. I am not a VB programer

    [ This Message was edited by: Puntarenas on 2002-05-02 16:19 ]

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ...I get a 'circular reference' error.
    could you provide some more information including cell refs and exactly what you want to have the formula return?

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Anno,
    Sorry taken so long to reply (traveling).
    Basically, what I am trying to do is to leave the value of the cell being tested intact unless the value of another cell matches a criteria
    Ex.
    A2= 8
    A5= Y
    I want to test cell A2 so that if A5 = Y change the value to 10 else leave 8.
    Does this help?
    JAC

  7. #7
    Board Regular sen_edp's Avatar
    Join Date
    Mar 2002
    Location
    Hellas
    Posts
    555
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello Jac

    take a look, is this what you mean or am i missing something?

    Microsoft Excel - HtmlMaker.xls
    File(F) Edit(E) View(V) Insert(I) Tool(T) Data(D) Window(W) Help(H)
    A1=
    ABCDE
    1
    2:alert('10')>=IF(A5="Y",10,8)
    3
    4
    5Y
    6
    7
    8
    9
    Sheet1

    You can see the value of cells only click each above hyperlinks



    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    -----------------------------------------------------------------------------------------------------------------------------------------
    ANDREAS ( WINDOWS 7 PRO, MICROSOFT EXCEL 2010)

  8. #8
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Adrian,
    Thank you for your response. You almost had it right. Using your example:
    For the value of A2:
    If A5 = 'Y' then place the value of '10' in A2 else place whatever the current value of A5 in A2.
    JAC

    [ This Message was edited by: Puntarenas on 2002-06-10 12:58 ]

    [ This Message was edited by: Puntarenas on 2002-06-10 12:59 ]

    [ This Message was edited by: puntarenas on 2002-07-11 17:01 ]

  9. #9
    New Member
    Join Date
    Jan 2007
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default If true, do something, do nothing (NOT "" )

    This is my question also. I want to test the value of a cell. If it is void, empty, my If statement will be true and text will be entered. If it is false, ie there is text or numbers in the cell, (and there ussally will be), I want it left alone. Something like
    a b c
    1
    2 45 $1 1
    3 46 $2 2
    IF(A1="","Page " &C2". do nothing

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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