Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post value in remote cell (Excel 2003)

    I was wondering if you can "post" a value in another cell without having a formula in the remote cell?

    Say I have a IF formula in cell A evaluating certain criteria which will give an answer of 1 if true. Is there a way of posting the answer in cell B without having a formula in cell B?

    Does the question makes sence?

    Regards

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Post value in remote cell (Excel 2003)

    You can do it with a macro: the macro can run when cell A is modified to put a value in Cell B. It can not be done with formulas.

    What are you trying to accomplish?

    Steve

  3. #3
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Post value in remote cell (Excel 2003)

    I was trying to help a friend of mine who bet on horses and dogs as a hobby. He has a spreadsheet and an internet interface communicating with each other, when certain things happen he has to manually type the value -1 into a cell which then trigger another action, but I got him to type out exactly what he is doing so I will just past his explanation in here.

    My spreadsheet receives live information from a betting interface for horse and dog races.

    The live information feeds into columns A-P, by as many rows as are necessary to cover the number of runners in the race.

    In column Q I have an IF statement. If my conditions are met, the stake money inserted by me in Col S is bet at the odds entered by me in Col R. That area of the spreadsheet contains hidden macros to send my request back to the interface. If my bet is placed, a record is fed back to cols T-X.

    When the race starts, "Suspended" is fed from the interface to cell F2.

    At any time I can type -1 into cell Q2 to move to the next race on a pre-selected list on the interface. (The -1 disappears as soon as "enter" is pressed)

    What I want is something to automatically type -1 into Q2 as soon as "Suspended" appears in F2.

    Then I can set up the list of races I want to consider for the day, and leave my conditional statements to control everything with no regular action by me to move on to the next race every time.

    Regards

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Post value in remote cell (Excel 2003)

    I assume that the live information is a web query. To react to this query being updated (automatically or manually), you'd need to write code for the AfterRefresh event of the querytable. See Using Events with the QueryTable Object and AfterRefresh Event.

    The idea would be:
    - Create a class module clsQT.
    - Put code in this class module:

    Public WithEvents qt As QueryTable

    Private Sub qt_AfterRefresh(ByVal Success As Boolean)
    If Success Then
    If qt.Parent.Range("F2") = "Suspended" Then
    qt.Parent.Range("Q2") = -1
    End If
    End If
    End Sub

    Public Sub InitQT(obj As Object)
    Set qt = obj
    End Sub

    - Insert the following declaration in a (new or existing) standard module:

    Public cls As New clsQT

    - Create a Workbook_Open event in the ThisWorkbook module (or add to the existing code if already present):

    Private Sub Workbook_Open()
    cls.InitQT Worksheets("Betting").QueryTables(1)
    End Sub

    where Betting is the name of the worksheet.

    Note: I'm not sure that placing -1 in cell Q2 by code will trigger the desired action. If not, you can call the appropriate macro instead of or in addition to setting Q2 to -1.

  5. #5
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Post value in remote cell (Excel 2003)

    Hans

    Thank you very much for your valued reply.

    We will try and get it working and report back to you.

    Have a nice weekend.

    Regards

Posting Permissions

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