Circular Reference

  • Thread starter Thread starter munim
  • Start date Start date
M

munim

Hello group!

For instance, I've a workbook and it has two sheets. Suppose, I want t
link A1(sheet1) and a1(sheet2). *If I update any of the cells in any o
the sheets the other one will be updated automatically.* How can thi
be possible? Or are there any other alternatives?

TIA
Muni
 
Niek said:
What formulas? What are you trying to achieve?
Click to expand...
It is not about formulae... I want to link two cells and whatever I pu
into one of the cells it will automatically update the other one..
 
It is not about formulae... I want to link two cells and whatever I put
into one of the cells it will automatically update the other one...
Click to expand...

"Links" as described above are formulae

Perhaps something along these lines ..

Suppose we want to link what's in Sheet1's A1:C5
in Sheet2's A1:C5

In Sheet2,
Put in A1: =IF(Sheet1!A1="","",Sheet1!A1)
Copy A1 across to C1, fill down to C5

Sheet2's A1:C5 will reflect inputs/updates made within Sheet1's A1:C5

Any empty cells within the source range in Sheet1
will also appear "empty" in Sheet2's destination range

---

And if we want to link it in Sheet2 to always "replicate" Sheet1
even with "future" insertions of rows / cols within the source range in
Sheet1

In Sheet2,

Put instead in A1:
=IF(OFFSET(Sheet1!$A$1,ROW()-1,COLUMN()-1)=0,"",
OFFSET(Sheet1!$A$1,ROW()-1,COLUMN()-1))

Copy A1 across to say, H1, fill down to H100 (say)

Fill a range in Sheet2 large enough to accommodate the
max expected "expansion"* of the original "source" range in Sheet1
*via subsequent insertion of rows/cols within the original range

Sheet2 will return the required results from Sheet1

For formats, do a Copy > Paste special > Formats > OK
(Copy from Sheet1, Paste special into Sheet2.
This part is manual but should be simple to execute)

(Formulas will not "copy" formatting over)
 
This is not exactly what I want...

I want something like....

I've sort of two cells in different sheets... if i update one of th
cells then it will automatically update the other one... and VISE VERS
 
I've sort of two cells in different sheets... if i update one of the
cells then it will automatically update the other one... and VISE VERSA
Click to expand...

Think you need vba for this. Hang around for insights from others.
 
munim wrote...
This is not exactly what I want...

I want something like....

I've sort of two cells in different sheets... if i update one of the
cells then it will automatically update the other one... and VISE VERSA
Click to expand...
....

Select both worksheets, that is, with one of these worksheets as the
active 'tab', hold down a [Ctrl] key and click on the other worksheet's
tab. Make sure that when one of these two worksheets is active, the
other is selected. Then *EVERYTHING* you do in one will be done in the
other. You could automate ensuring they're both selected using an event
handler in the ThisWorkbook class module. For example,


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Const WKSCOL As String = "Sheet1:Sheet3"

On Error GoTo End_Proc
Application.EnableEvents = False
Application.ScreenUpdating = False

If InStr(1, WKSCOL, Sh.Name) > 0 Then
Sheets(Split(WKSCOL, ":")).Select
Sh.Activate
End If

End_Proc:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub


If you're running Excel 8 (97) or prior, add the following additional
code.


#If Not VBA6 Then
Private Function Split(s As String, c As String) As Variant
Dim rv() As String
Dim k As Long, n As Long, p As Long, q As Long

k = 0
n = 7
ReDim rv(0 To n)

q = 1
p = InStr(q, s, c)

Do While p > 0
rv(k) = Mid(s, q, p - q)
q = p + Len(c)
p = InStr(q, s, c)
k = k + 1

If k >= n Then
n = n + k + 1
ReDim Preserve rv(0 To n)
End If
Loop

rv(k) = Mid(s, q)

ReDim Preserve rv(0 To k)
Split = rv
End Function
#End If
 
I think I couldn't make you understand what I want...

Let me be clear...

For Instance, I select a cell sheet1!A1 and another sheet2!B5... If
put anything in sheet2!B5, it will automatically update sheet1!A1 an
if I put sheet1!A1 then I will update sheet2!B5...

THEME

I've a sheet and it has an outlook all the calculations are don
there... and I have another sheet which has almost the same values wit
a different outlook.

So if I update any values of any of the sheet it will update the othe
sheet. Though It is has total different look. And the links in sheet
and sheet2 are random... i.e. sheet1!A5 -> sheet2!B3; sheet1!C6 -
sheet2!A10 goes on...

TI
 
Hi munim,

My advice would be to redesign your application. Try putting together a more
manageable concept.

If it is that difficult to explain to others and you don't know how to do
the job yourself you're definitely on a dead end track.
 
Thanks Niek Otten,

My problem ain't that complex... All I want is a mirror copy of a shee
with a different outlook... :( :(
 
I agree with Niek. While I'm sure it would be possible with some VB
code associated with Worksheet_Change or Workbook_Change events to d
exactly what you want, in keeping with the KIS principle, the simples
approach will be one where you designate one of the sheets as th
input/change sheet and the other sheet designated as the mirror shee
(maybe protected to prevent someone from changing cell formulas on th
mirror sheet)
 
munim wrote...
....
My problem ain't that complex... All I want is a mirror copy of a sheet
with a different outlook... :( :(
Click to expand...
....

Not so. Reread your own words . . .

"For Instance, I select a cell sheet1!A1 and another sheet2!B5... If I
put anything in sheet2!B5, it will automatically update sheet1!A1 and
if I put sheet1!A1 then I will update sheet2!B5..."

You don't understand that what you describe *IS* complex. Perhaps
that's why you can't figure out how to do it yourself. A true *MIRROR*
copy is pretty simple - all cells in one worksheet are *exactly* like
the same cell in the mirror worksheet. However, what you want is 2-way
links between different cells in different worksheets.

At a minimum, you'd need a 2 column table with Sheet1 references in the
1st column and corresponding Sheet2 references in the 2nd column. So
given your example above,

Sheet1!A1 Sheet2!B5

Then you'd need a SheetChange event handler in the workbook's
ThisWorkbook class module that checks whether changed cells appear in
the table, and if so makes an exact copy of the changed cells in the
corresponding cells. As long as all you're entering are constants and
formulas without any relative references, not too difficult. But what
should happen if the user enters

=X99

in Sheet1!A1? Should the Sheet2!B5 formula be

=Sheet1!X99

=Sheet1!Y103

=X99

=Y103

or

=Sheet1!A1

?

For that matter, what should happen if the user enters =Sheet2!B5 as
the formula in Sheet1!A1?

It's a very basic tenet of software development that multiple entry of
the same information is INVARIABLY a *BAD* thing. Redesign your
application so that one or the other of your 'mirror' worksheets is the
sole entry worksheet or allow entry in *neither* but instead use other
worksheets or user forms for *common* entry.

You could use some trickery to accomplish this. Cells Sheet1!A1 and
Sheet2!B5 could both reference Sheet99!X99, and you could use a
SheetChange event handler to check if the changed cell is either
Sheet1!A1 or Sheet2!B5, and if so store the entered value in a VBA
variable, back out the entry in the cell in which is was made using
Application.Undo, then put the stored entry into Sheet99!X99. You'd
still have the specification questions above to resolve, but you'd have
a single common place for data storage even though you could make
entries from multiple places.

IMO, this form of trickery is more trouble than it's worth, but I also
believe that experience is the best teacher, and you seem to need
instruction about not playing with fire.
 
I know I can change the structure of the program... I find it more
flexible if I can update in the both way... As you said it is like
playing with *fire*.

As I'm a newbie at Excel... I dont' know whether it is impossible or
not.

I would like to know, can it be done? I know it is very much simple to
make a mirror of *two* sheets and both way updatable.

Ain't there any ways to link two sheets and can be updated from the
both ends? Can't it be done using *Circular Reference*?
 
munim wrote...
I know I can change the structure of the program... I find it more
flexible if I can update in the both way... As you said it is like
playing with *fire*.
Click to expand...

To repeat: making identical entries in multiple places is a bad idea.
It may seem flexible, but the eventual trouble invariably exceeds any
perceived benefits.
As I'm a newbie at Excel... I dont' know whether it is impossible or
not.

I would like to know, can it be done? I know it is very much simple to
make a mirror of *two* sheets and both way updatable.
Click to expand...

It's possible, but it *REQUIRES* programming/VBA.

For example, create a 3-column table named RefTbl in another worksheet
and fill it's first two columns with corresponding range addresses,
e.g., for 2 such ranges,

Sheet1!A1 Sheet2!B5
Sheet1!C3 Sheet2!B6

Make sure all cells in the 3rd column in this table are unprotected.
Then add the following event handler to the workbook's ThisWorkbook
class module.


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Dim rt As Range, s As Range, wsa As String, k As Long

On Error GoTo Exit_Proc
Application.EnableEvents = False
Application.ScreenUpdating = False

Set rt = Me.Names("RefTbl").RefersToRange
wsa = Target.Parent.Name & "!" & Target.Address(0, 0, xlA1, 0)

With Application.WorksheetFunction
If .CountIf(.Index(rt, 0, 1), wsa) > 0 Then
k = .Match(wsa, .Index(rt, 0, 1), 0)
ElseIf .CountIf(.Index(rt, 0, 2), wsa) > 0 Then
k = .Match(wsa, .Index(rt, 0, 2), 0)
Else
k = 0
End If

If k > 0 Then
Set s = .Index(rt, k, 3)
s.Value = Target.Value
Range(.Index(rt, k, 1).Value).Formula = "=" & s.Address(1, 1,
xlA1, 1)
Range(.Index(rt, k, 2).Value).Formula = "=" & s.Address(1, 1,
xlA1, 1)
End If
End With

Exit_Proc:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub


The value of whatever you enter in any of the cells listed in the 1st 2
columns of RefTbl will be stored in the 3rd column of RefTbl and the 2
corresponding cells will refer to the value in the 3rd column in
RefTbl.
Ain't there any ways to link two sheets and can be updated from the
both ends? Can't it be done using *Circular Reference*?
Click to expand...

No, it can't be done using circular references.

Ponder this for a moment: if Sheet1!A1 contains the formula =Sheet2!B5,
and Sheet2!B5 contains the formula =Sheet1!A1, neither can contain the
value to which both should evaluate because they both need to contain
references to the other instead. That is, it's *IMPOSSIBLE* to store a
formula and a value in the same cell. If 2 cells should always evaluate
to the same value, then either one needs to hold the value and the
other refer to the former or both need to refer to a 3rd cell that
contains the common value.

There's NO WAY TO DO THIS without programming, so there's no SIMPLE way
to do this. Bemoan this fact it you will, keep asking for alternatives
if you must, but eventually you may realize that what you want to do
isn't simple.
 
Thanks a lot man.... You are a great help!!!

It worked it out fine... My intensions are to make a sheet with
different look... And can be updatable from any ends...

:)

Take care
Munim
 
Back
Top