excel circulating reference

June 24, 2009 at 11:18:52
Specs: Windows XP
I have 2 spread sheets with some of the same information. i am wanting to be able to enter a date in a cell on 1st spread sheet and have copy to the corresponding cell on the 2nd sheet and to be able to enter the date on the 2nd sheet to be copied back to the corresponding cell on the 1st sheet. I know this is called a circulating reference. I there a way to do this without Macros

See More: excel circulating reference

Report •


#1
June 24, 2009 at 11:57:51
The term is "circular reference", not "circulating reference".

However, what you are asking for is not really a circular reference.

Bear with me for a second...

A circular reference can be defined as follows:

"Circular references occur when a formula refers--either directly or indirectly--to the cell in which the formula is stored."

In your case, you might start with a circular reference:

Sheet1!A1 contains =Sheet2!A1
Sheet2!A1 contains =Sheet1!A1

but as soon as you enter a date in ether of those cells, you remove the circular reference since only one cell will contain a formula.

It's a subtle difference, I'll agree, but since a cell can't contain both "text" and a formula, the instant you try to use your configuration, the circular reference goes away.

However, all that blabber doesn't help you accomplish your goal, does it? <g>

Unfortunately, I don't think you'll be able to do what you want without some VBA code. There are no Excel functions that allow two cells to reflect the contents of each other and allow you to change the contents of either of those cells whenever you want.

Something as simple as a Selection_Change macro with an InputBox would do the trick. Put this in the VBA editor for both Sheet1 and Sheet2 and it'll fire whenever you select A1 in either sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Target.Address = "$A$1" Then
  myDate = Application.InputBox("Enter A Date", "Date Entry")
  Sheets(1).Range("A1") = myDate
  Sheets(2).Range("A1") = myDate
 End If
End Sub


Report •

#2
June 25, 2009 at 06:03:39
thanks that is what i am looking for, however I am wanting to be able to do this to more than 1 cell. How would I do a range of cells but not in consecutive order

Report •

#3
June 25, 2009 at 06:44:27
This should work for A1, B3 & C5. Modify as required.

If "Not Intersect(Target, myRange)" is Nothing then it is something and the Target "intersects" with the referenced range.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Set myRange = Range("A1,B3,C5")
  If Not Intersect(Target, myRange) Is Nothing Then
   myDate = Application.InputBox("Enter A Date", "Date Entry")
   Sheets(1).Range(Target.Address) = myDate
   Sheets(2).Range(Target.Address) = myDate
  End If
End Sub


Report •

Related Solutions

#4
June 25, 2009 at 07:36:24
Yes, I really like the way this works but need to know how to do if the columns and rows are different between the sheets. For instance sheet 1 date at A1 and need to have copy on sheet 2 at D8, and then sheet 2s D8 back to sheet 1s A1 and I am needing to do this to more than just 1 cell needing multiple cell like this.

Report •


Ask Question