Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have two sheets.

In the first sheet, cell F4, I have 00:00:00 (countdown). G9, G10 and G11 are cells that receive live data (decimal numbers).

In the second sheet, I have three cells linked from sheet1, G9 ='Sheet1'!G9, G10 ='Sheet1'!G10, G11 ='Sheet1'!G11 (which update themselves when data is modified in the first sheet).

Now I want to set in sheet 2, (assume) cells B9, B10 and B11 to show me (copy) the values from G9, G10 and G11 from sheet 1 when the countdown was 00:00:05 (5 seconds before Start) and not update again if the data changes in the cell it pulled the data from.

Like G9 ='Sheet1'!G9 at 00:00:05 and stop here, do not update anything. OK?

I can do a part, but the real problem is: I can not make it stop cells to update. Stand frozen, freeze, not move, calm .. however.

I do not want to seem pretentious (but my knowledge in excel is limited), the most appropriate would be a formula, not macro or VBA, if possible.

I want to post a picture but I can not because of my restrictions. Well, if this is not possible with a formula is just fine with (not really) VBA.

share|improve this question

migrated from Sep 2 '12 at 12:53

This question came from our site for professional and enthusiast programmers.

How does the countdown work? How is start defined? Not sure this can be done with formulas alone. VBA may make this possible – Remnant Sep 2 '12 at 11:14
It seems the condition to update is G9 >= 00:00:05 isn't it? – Jack Sep 2 '12 at 11:33
Hi all and thank you for the interest showed.Yes this is the condition. – Anka Sep 2 '12 at 23:21
It's so difficult? – Anka Sep 3 '12 at 17:41

I have a partial solution.  The following VBA function:

Sub conditional_update()
    If ((Worksheets("Sheet2").Range("F4").Value * 24 * 60 * 60) >= 5) Then
        Worksheets("Sheet2").Range("B9").Value  = Worksheets("Sheet2").Range("G9").Value
        Worksheets("Sheet2").Range("B10").Value = Worksheets("Sheet2").Range("G10").Value
        Worksheets("Sheet2").Range("B11").Value = Worksheets("Sheet2").Range("G11").Value
    End If
End Sub

will update the B9-B11 cells if and only if F4 >= 0:00:05.  Ideally, this function should be invoked every time anything changes.  Offhand, I don't know how to do that.

This is copying the data from G9-G11 in Sheet2, which you have linked to the corresponding cells on Sheet1.  If you want the function to pull data directly from Sheet1, you can make the obvious change.

share|improve this answer
Good evening Scott and thanks for your time. I put your code in my sheet and does not work. Maybe I not put in the right place? Anyway, I exposed my problem in another forum where I found the solution. The initial sheet was slightly modified to make it easier to solve. If another newbie like me, needs the solution, I post the link here. I will come back with more questions. Now I began to study in detail Excel. See you soon. – Anka Sep 7 '12 at 19:33

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .