How to make real linked cells, not with formulas? 1

[McJe]

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.

 

[Terrifyingtesttube]

If you aren't opposed to using macros, then yes. In this example I am making the values in Sheet1 and Sheet2 cell B1 be the same.

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

 

[TomBarsh]

What you ask is not possible. The required tubesheet thicknesses by TEMA rules and by ASME UHX rules will be different because the rules are different. How can you "solve" for the thickness based on both sets of rules when the rules will provide different thicknesses?

 

[MechEng2005]

A very inconvient way of doing this would be:

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

 

[GregLocock]

or use a third sheet that drives the other two and reports their results.



Cheers

Greg Locock

SIGlease see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[McJe]

Thanks clindeman, I was thinking like this also, I only wanted to be sure there isn't a quick and easy way in excel to do this. I'll use this method.

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

 

[TomBarsh]

OP, What you originally proposed was not possible (to have same numerical value of thickness) because the TEMA and UHX thickness values will be different. But it is almost trivial to allow each thickness (TEMA and UHX) to be calculated to meet their respective rules, then select the greater of those two thicknesses as your final design value; either make the selection of the larger by human inspection, or add another sheet (or few more cells) to show the TEMA value, the UHX value, "And the design value is the larger of the two, design = ".

Try using either the Excel Goal Seek or Solver function to determine the individual TEMA and UHX values.