Circular Reference-How can this be possible?

Hello group!

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

TIA
Munim

What formulas? What are you trying to achieve?

--
Kind regards,

Niek Otten

"munim" <[email protected]> wrote in
message news:[email protected]...
>
> Hello group!
>
> For instance, I've a workbook and it has two sheets. Suppose, I want to
> link A1(sheet1) and a1(sheet2). *If I update any of the cells in any of
> the sheets the other one will be updated automatically.* How can this
> be possible? Or are there any other alternatives?
>
> TIA
> Munim
>
>
> --
> munim
> ------------------------------------------------------------------------
> munim's Profile:
> http://www.excelforum.com/member.php...o&userid=30052
> View this thread: http://www.excelforum.com/showthread...hreadid=497307
>

Originally Posted by Niek Otten

What formulas? What are you trying to achieve?

--
Kind regards,

Niek Otten

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...

> 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...

"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)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

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

> 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

Think you need vba for this. Hang around for insights from others.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

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
....

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