Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Circular Reference
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Circular Reference
What formulas? What are you trying to achieve?
-- Kind regards, Niek Otten "munim" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Circular Reference
Niek Otten Wrote: 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... -- munim ------------------------------------------------------------------------ munim's Profile: http://www.excelforum.com/member.php...o&userid=30052 View this thread: http://www.excelforum.com/showthread...hreadid=497307 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Circular Reference
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 -- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Circular Reference
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 -- munim ------------------------------------------------------------------------ munim's Profile: http://www.excelforum.com/member.php...o&userid=30052 View this thread: http://www.excelforum.com/showthread...hreadid=497307 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Circular Reference
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 -- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Circular Reference
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Circular Reference
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 I put anything in sheet2!B5, it will automatically update sheet1!A1 and 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 done there... and I have another sheet which has almost the same values with a different outlook. So if I update any values of any of the sheet it will update the other sheet. Though It is has total different look. And the links in sheet1 and sheet2 are random... i.e. sheet1!A5 - sheet2!B3; sheet1!C6 - sheet2!A10 goes on... TIA -- munim ------------------------------------------------------------------------ munim's Profile: http://www.excelforum.com/member.php...o&userid=30052 View this thread: http://www.excelforum.com/showthread...hreadid=497307 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Circular Reference
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. -- Kind regards, Niek Otten "munim" wrote in message ... 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 I put anything in sheet2!B5, it will automatically update sheet1!A1 and 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 done there... and I have another sheet which has almost the same values with a different outlook. So if I update any values of any of the sheet it will update the other sheet. Though It is has total different look. And the links in sheet1 and sheet2 are random... i.e. sheet1!A5 - sheet2!B3; sheet1!C6 - sheet2!A10 goes on... TIA -- munim ------------------------------------------------------------------------ munim's Profile: http://www.excelforum.com/member.php...o&userid=30052 View this thread: http://www.excelforum.com/showthread...hreadid=497307 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Circular Reference
Thanks Niek Otten, My problem ain't that complex... All I want is a mirror copy of a sheet with a different outlook... :( :( Niek Otten Wrote: 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. -- Kind regards, Niek Otten -- munim ------------------------------------------------------------------------ munim's Profile: http://www.excelforum.com/member.php...o&userid=30052 View this thread: http://www.excelforum.com/showthread...hreadid=497307 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Circular Reference
That is not difficult at all as long as you change only one of the sheets.
-- Kind regards, Niek Otten "munim" wrote in message ... Thanks Niek Otten, My problem ain't that complex... All I want is a mirror copy of a sheet with a different outlook... :( :( Niek Otten Wrote: 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. -- Kind regards, Niek Otten -- munim ------------------------------------------------------------------------ munim's Profile: http://www.excelforum.com/member.php...o&userid=30052 View this thread: http://www.excelforum.com/showthread...hreadid=497307 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Circular Reference
I agree with Niek. While I'm sure it would be possible with some VBA code associated with Worksheet_Change or Workbook_Change events to do exactly what you want, in keeping with the KIS principle, the simplest approach will be one where you designate one of the sheets as the input/change sheet and the other sheet designated as the mirror sheet (maybe protected to prevent someone from changing cell formulas on the mirror sheet). -- MrShorty ------------------------------------------------------------------------ MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181 View this thread: http://www.excelforum.com/showthread...hreadid=497307 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Circular Reference
munim wrote...
.... My problem ain't that complex... All I want is a mirror copy of a sheet with a different outlook... :( :( .... 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. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Circular Reference
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*? Harlan Grove Wrote: munim wrote... .... My problem ain't that complex... All I want is a mirror copy of a sheet with a different outlook... :( :( .... 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. -- munim ------------------------------------------------------------------------ munim's Profile: http://www.excelforum.com/member.php...o&userid=30052 View this thread: http://www.excelforum.com/showthread...hreadid=497307 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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*. 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. 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*? 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. |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Circular Reference
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 -- munim ------------------------------------------------------------------------ munim's Profile: http://www.excelforum.com/member.php...o&userid=30052 View this thread: http://www.excelforum.com/showthread...hreadid=497307 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Iterate Circular Reference | Excel Discussion (Misc queries) | |||
Highest Value / Circular Reference Help | Excel Discussion (Misc queries) | |||
Highest Value / Circular Reference Help | Excel Worksheet Functions | |||
Help solve a Circular Reference | Excel Worksheet Functions | |||
Circular reference | Excel Discussion (Misc queries) |