×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

How to make real linked cells, not with formulas?

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.

RE: How to make real linked cells, not with formulas?

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

 

RE: How to make real linked cells, not with formulas?

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?

RE: How to make real linked cells, not with formulas?

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

RE: How to make real linked cells, not with formulas?

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

 

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?

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

RE: How to make real linked cells, not with formulas?

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources