How to make real linked cells, not with formulas?
How to make real linked cells, not with formulas?
(OP)
I have an excel workbook with different sheets.
On 1 sheet, I calculate the thickness of a fixed tubesheet according to TEMA, on another sheet I do the same for ASME-UHX.
All of the data that is used for both calculations is 'linked' with a formula ( ='sheet1'C2 for example )
But, there is one value that needs to be changed many times to find the ideal value: the tubesheet thickness.
Currently, the thickness is entered on the 1st sheet, and 'linked' by a formula to a cell on the 2nd sheet.
So, it isn't possible to change this value on the 2nd sheet, without overwriting the 'linking'- formula. And this isn't allowed, because the thickness should always be the same for both calculations.
Nowadays we have to go back and forward to the 1st sheet to change the thickness.
Isn't there a way to 'clone' 2 cells? I mean a way to tell Excel to make sure both cells on sheet 1 and 2 have ALWAYS the same value? Is I change the value on sheet 1, the cell on sheet 2 changes simultaniously. And if I change the value on sheet 2, the cell on sheet 1 changes.
Thanks for your ideas!
Jeroen.
On 1 sheet, I calculate the thickness of a fixed tubesheet according to TEMA, on another sheet I do the same for ASME-UHX.
All of the data that is used for both calculations is 'linked' with a formula ( ='sheet1'C2 for example )
But, there is one value that needs to be changed many times to find the ideal value: the tubesheet thickness.
Currently, the thickness is entered on the 1st sheet, and 'linked' by a formula to a cell on the 2nd sheet.
So, it isn't possible to change this value on the 2nd sheet, without overwriting the 'linking'- formula. And this isn't allowed, because the thickness should always be the same for both calculations.
Nowadays we have to go back and forward to the 1st sheet to change the thickness.
Isn't there a way to 'clone' 2 cells? I mean a way to tell Excel to make sure both cells on sheet 1 and 2 have ALWAYS the same value? Is I change the value on sheet 1, the cell on sheet 2 changes simultaniously. And if I change the value on sheet 2, the cell on sheet 1 changes.
Thanks for your ideas!
Jeroen.





RE: How to make real linked cells, not with formulas?
In the VBA window (Alt+F11) go to the code for Sheet1
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$B$1" Then
Sheets("Sheet2").Range("b1").Value = Sheets("Sheet1").Range("b1").Value
End If
End Sub
In the code for Sheet2
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$B$1" Then
Sheets("Sheet1").Range("b1").Value = Sheets("Sheet2").Range("b1").Value
End If
End Sub
RE: How to make real linked cells, not with formulas?
RE: How to make real linked cells, not with formulas?
Sheet 1, Cell A1: <User input Tube thickness>
Sheet 1, Cell A2: =Now()&"__"&A1
Sheet 1, Cell A3: A formula that compares the "Now" portion of Cells A2 in each sheet, and returns the value of A1 for the the sheet with a greater value for the "Now" portion.
Sheet 2 would have the same type of thing. Then, when cell A1 is updated on either sheet, the time stamp in cell A2 is also updated. The formula in cell A3 would determine the most recently entered value and use that one. I don't have time now to figure out exactly what the formula would be needed for the cell in A3, but I'm pretty sure it could be done. If this is really the route you want to take and need help with it, let me know.
-- MechEng2005
RE: How to make real linked cells, not with formulas?
Cheers
Greg Locock
SIG:Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: How to make real linked cells, not with formulas?
TomBarsh, why wouldn't that be possible?
If a customer requires a heat exchanger according to TEMA and UHX, then I need a tubesheet thickness that is OK for both codes.
The resulting thickness would be the minimal thickness for 1 code, and would be thicker then the minimum for the other code, but both nevertheless it must be OK for both.
What we normally do is search for the minimum allowed thickness for TEMA, and then check this for UHX. If the thickness is not OK for UHX we search for a value that fits both. (our TEMA sheet is much faster then the UHX sheet)
Most of the times, adjusting the TS thickness will do, but sometimes other values need to be changed as well of course (shell thickness, tube thickness, ...)
RE: How to make real linked cells, not with formulas?
Try using either the Excel Goal Seek or Solver function to determine the individual TEMA and UHX values.