[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 912
  • Last Modified:

Latch a Value with No Circular Reference Error in Excel 2010

Excel 2010 problem:
My question is quite close to the "Excel Circular Reference" question.

                   Col D        Col E         Col F
Row 18       1 or 0      (value)        =IF(D18=1,E18,F18)

What I am trying to get Excel to do is latch in the value of E18 into F18 on command:
"If D18=1. make F18 a copy of the value of E18;
 when I set D18=0, leave F18 as-is."

Since there seems to be no way of telling Excel to do nothing if D18 is changed from 1 to 0,
I am telling Excel to copy the current contents of F18 to F18 (thus leaving it unchanged).
This does what I want, but results in a circular reference error.
How do I avoid the circular reference error?  In other words,

Is there any way to
1. put a value in E18
2. set D18 to 1 to get the value copied to F18
3. set D18 to 0 leaving the copied value in F18
4. change the value of E18 some time later
5. set D18 to 1 to latch in the new E18 value in F18
6. set D18 to 0 leaving the copied value in F18
etc. without getting a circular error message?
0
Theodore Dinsmore
Asked:
Theodore Dinsmore
  • 5
  • 4
1 Solution
 
Martin LissRetired ProgrammerCommented:
Instead of a formula, try this.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$D$18" Then
        If Target.Value = 1 Then
            Range("F18").Value = Range("E18").Value
        End If
    End If

End Sub

Open in new window

0
 
Theodore DinsmoreAuthor Commented:
' adapting the above to the actual problem (with little knowledge of correct syntax):
Private Sub Worksheet_Change(ByVal Target As Range)
  ' if target is the general reset request cell V2 then
    If Target.Address = "$V$2" Then
  '     if general reset is requested then
        If Target.Value = 1 Then
  '         mark all cells in this range as reset
            Range("P169:IU440").Value = "x"
  '         mark cell O440 as reset
            Range("O440").Value = "x"
        End If
    Else
      ' if target is a month date like 10/1/2014 in row 149
      '           in some column between P and IU then
        If Target.Address >= "P149" Then
            If Target.Address <= "IU149" Then
      '         if set values for this date is requested by cell S2 then
                If Range("$S$2").Value = 1 Then
      '             set cell rows 169-439 in same column as date
      '                  to the column N value in that row
                    Range("<target column>169:<target column>439").Value
                         = Range("N169:N439").Value
      '             set this cell to N440
                    Range("<target column-1>O440").Value = "$N$440"
                End If
            End If
        End If
    End If
End Sub

' Will someone help me express the above in correct syntax?
' Is a For ... Next loop or equivalent needed
'     to set a column of cells to a column N values?
' How does one get at and use the row number of the Target.Address?
0
 
Martin LissRetired ProgrammerCommented:
What does your latest post have to do with your original question?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Theodore DinsmoreAuthor Commented:
OK, I guess asking how to use the answer to my original question is not the original question.
Your original answer, I am quite sure is the solution to the question I asked.
However, I cannot actually verify that it is, because I have no clue how to "try this."
I enabled the Developer tab in Excel assuming your solution is a macro, but it does not
show a "Code" section.  From looking at the Excel helps under macros, it seems I may need
to install Visual Basic...
Anyhow, I accept your solution.
0
 
Theodore DinsmoreAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for trdinsmore's comment #a40264260

for the following reason:

Sorry, I meant to accept the expert's comment.  He immediately provided a solution. (However, I am not knowledgeable enough to use it.)
0
 
Martin LissRetired ProgrammerCommented:
Your original answer, I am quite sure is the solution to the question I asked.
However, I cannot actually verify that it is, because I have no clue how to "try this."
1.    Remove your formula from F18
2.    Type Alt+F11 to get to the Visual Basic IDE
3.    On the left you will see a list of your sheets and you should double-click the one where you have the problem
4.    On the left of the window that is displayed you will see a drop-down list with "(General)" showing. Open the list and select "Worksheet"
5.    On the right of the window that is displayed you will see a drop-down list with "SelectionChange" showing. Open the list and select "Change"
6.    Add lines 3 to 7 from my post ID 40260977 to the Worksheet_Change sub
7.    Go back to Excel and see if that fixes the problem. If it doesn't then if you want to, please 'Request Attention' and have the points removed from my answer.
0
 
Theodore DinsmoreAuthor Commented:
Thank you, MartinLiss, for the above comment.  It is more complete than what I discovered.
I gratefully accept your response.  

For those as unknowledgeable as I, I thought I would post how I found my way to "try it"
before receiving the MartinLiss comment:

1. I discovered that a macro is created by recording keystrokes and then invoking them
   by typing a special keystroke like Ctrl Shift Alt X.  It is not created by specifying
   a subroutine, as in the solution above.  Therefore the solution is not a macro.
2. I discovered that an unadvertised tab "Developer" exists and can be enabled by
   File -> Options -> Customize Ribbon and checking Developer.
3. I discovered that using the Developer tab starts with invoking its Code section; however,
   my Developer tab refuses to advertise such a section.
4. I discovered that that there is a keystroke Alt-F11 which does the same thing;
   it opens a Visual Basic Application window.
5. On discovery that Visual Basic is built into Excel, I realized that the solution above
   not only looks like Visual Basic code, it IS Visual Basic code, a subroutine.
To "try it",
7. At left, I selected the worksheet to install the subroutine to
8, I used Insert -> Procedure and selected Sub and (optionally) Private
9. In the resulting window showing the basic form of the Sub,
   I re-specified the subroutine the way the above solution expresses it
10. In the Debug tab, I clicked Compile
11. I set up the worksheet line 18 as the question describes it
12. I set D18 = 1 and lo and behold, F18 became what was in E18.
13. By experimenting, I discovered
    that Range("O169:O439").Value = Range("N169:N439").Value
        will indeed copy a column to another column without a For ... Next loop, and
    that Target.Row exists and Range("G18") = Target.Column returns the Target column.
0
 
Theodore DinsmoreAuthor Commented:
Further Discoveries:
Target.Address is target address within this worksheet,
      not all worksheets
Target.Address is the cell that was manually changed only,
      not a cell that changed as a result
Object Range() cannot accept a reference to a different worksheet

Final code uses Excel-specified ranges to reset or latch
      and an Excel-specified value to reset to

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$X$2" Then         ' if target is Reset All Overrides cell
        If Target.Value = 1 Then            '     if Reset All Overrides is requested
                                            ' Reset specified range to specified value
            Range(Range("$M$101").Value).Value = Range("$M$106").Value
            Range(Range("$M$102").Value).Value = Range("$M$106").Value
            Range(Range("$M$103").Value).Value = Range("$M$106").Value
        End If
    Else
        If Target.Address = "$T$2" Then     ' if target is Reset Overrides for Date cell
            If Target.Value = 1 Then        '     if Reset Overrides for Date is requested
                                            ' Reset specified range to specified value
                Range(Range("$M$104").Value).Value = Range("$M$106").Value
            End If
        Else
            If Target.Address = "$F$2" Then ' if target is Latch Overrides for Date cell
                If Target.Value = 1 Then    '     if Latch Overrides for Date is requested
                                        ' Latch specified range from specified range
                    Range(Range("$M$104").Value).Value = Range(Range("$M$105").Value).Value
                End If
            End If
        End If
    End If

End Sub
0
 
Martin LissRetired ProgrammerCommented:
Object Range() cannot accept a reference to a different worksheet
Sure it can. Range("A1") = Worksheets("SomeOtherSheetName").Range("B1")

And I'm surprised that Range(Range("$M$104").Value).Value = Range(Range("$M$105").Value).Value works. What is normally written is Range("$M$104").Value = Range("$M$105").Value or just Range("M104").Value = Range("M105").Value. The $ sign is used primarily in worksheet functions to indicate to VB that when the function is copy/pasted that (in this case) both the column and row are fixed.

Finally, I think you would be better understood if you said "Copy" rather than "Latch".
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now