need formula to work once
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: need formula to work once

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

    Default

     
    i have a logic formula that works great, but if its true i want the value to replace the formula automatically. this way when the target data changes it will not erase the previous answer.

    thanks eric

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You will need VBA to do this and you will need to modify this code to suit your situation.

    Right click on your sheet tab, left click on View Code, and paste this in:

    Private Sub Worksheet_Calculate()
    If [A1] = 5 Then
    [A1] = [A1]
    End If
    End Sub

    This code demonstrates the example that, let's say, you have a formula in A1 such as
    =B1+C1
    The first time that a combination of numbers is entered into B1 and C1 that equals 5, then a 5 will be displayed in A1 and the formula in A1 will be overridden.

    Hopefully this will help point you in the direction to accomplish this for your specific cell and True False equation.

    Tom Urtis


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

    Default

    thanks, i'll try it

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

    Default

    thanks for the formula

    Private Sub Worksheet_Calculate()
    If [A1] = 5 Then
    [A1] = [A1]
    End If
    End Sub

    but i want [a1] = (a cell on another sheet in the workbook) sheet1 [b2]
    how do i reference sheet1 ?
    thanks again!
    eric
    --

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Chippenham, UK
    Posts
    136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-26 16:00, ercstric wrote:
    thanks for the formula

    Private Sub Worksheet_Calculate()
    If [A1] = 5 Then
    [A1] = [A1]
    End If
    End Sub

    but i want [a1] = (a cell on another sheet in the workbook) sheet1 [b2]
    how do i reference sheet1 ?
    thanks again!
    eric
    --
    Use Range("[Book1.xls]Sheet1!A1").Value
    Where Book1.xls is the name of the workbook and Sheet1! is the name of the sheet and A1 is the cell.


    Regards,

    Gary Hewitt-Long

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

    Default

    i still have the problem ...

    the cell is not retaining the solution to the formula. once the target cell entry changes, the cell i want frozen just goes back to being false

    here is the formula:
    =IF(A9='File Master'!B23,'File Master'!B2:F2,"")

    a9 is a document number say 2002-06 (text format), then i want the title returned as the solution !b2:f2 if false then return empty cell ("")

    [ This Message was edited by: ercstric on 2002-02-26 20:23 ]

    [ This Message was edited by: ercstric on 2002-02-26 20:23 ]

  7. #7
    New Member
    Join Date
    Feb 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    OKAY!!!!!!!! I got it!!!!!!!!

    here's how it finally looked
    Private Sub Worksheet_Calculate()
    If [b9] = Sheet1.[B2] Then
    [b9] = [b9]
    End If
    End Sub


    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

    End Sub

    thanks to everyone, especially Tom Urtis

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
  •  

 

 
DMCA.com