Avatar of Janis Ozols
Janis Ozols
 asked on

Link sheets

Hello I have a little problem.
For example in sheet1 on C13 i have formula like 5.81*0.97 and in the sheet2 in C13 i have formula 888*0.97..
What i need to do to like link them, like if i change that (0.97 to 0.85) in sheet1 in C13 it will automatic change in sheet2 in C13 from 0.97 to 0.85 as well.
???
Microsoft ExcelMicrosoft OfficeSpreadsheets

Avatar of undefined
Last Comment
Saqib Husain

8/22/2022 - Mon
Ryan Chong

>>What i need to do to like link them, like if i change that (0.97 to 0.85) in sheet1 in C13 it will automatic change in sheet2 in C13 from 0.97 to 0.85 as well.
both need to refer to a static cell in order to do that, which mean you need to put the value of 0.85 to another cell, let both C13 in Sheet1 and Sheet2 refer to it.
28968462.xlsx
Saqib Husain

You can do this using a macro. Do you want to go in that direction?
Janis Ozols

ASKER
I could go that direction it would be really good for me.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Saqib Husain

Try this macro in the Workbook Module
Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Count = 1 Then
        If ActiveCell.Worksheet.Name = Target.Worksheet.Name Then
            If Sh.Name = "Sheet1" Or Sh.Name = "Sheet2" Then
                If Target.Address = Range("C13").Address Then
                    If Sh.Name = "Sheet1" Then
                        Sheets("Sheet2").Range("C13").Formula = Left(Sheets("Sheet2").Range("C13").Formula, InStr(Sheets("Sheet2").Range("C13").Formula, "*")) & Right(Target.Formula, Len(Target.Formula) - InStr(Target.Formula, "*"))
                    ElseIf Sh.Name = "Sheet2" Then
                        Sheets("Sheet1").Range("C13").Formula = Left(Sheets("Sheet1").Range("C13").Formula, InStr(Sheets("Sheet1").Range("C13").Formula, "*")) & Right(Target.Formula, Len(Target.Formula) - InStr(Target.Formula, "*"))
                    End If
                End If
            End If
        End If
    End If
End Sub

Open in new window

Change-constant-macro.xlsm
Janis Ozols

ASKER
Woow, it really helped, what can I do if range is from C13 to I13, how I need to change the Macros?
Saqib Husain

Change the macro to
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Count = 1 Then
        If ActiveCell.Worksheet.Name = Target.Worksheet.Name Then
            If Sh.Name = "Sheet1" Or Sh.Name = "Sheet2" Then
                If Not Intersect(Target, Range("C13:I13")) Is Nothing Then
                    If Sh.Name = "Sheet1" Then
                        Sheets("Sheet2").Range(Target.Address).Formula = Left(Sheets("Sheet2").Range(Target.Address).Formula, InStr(Sheets("Sheet2").Range(Target.Address).Formula, "*")) & Right(Target.Formula, Len(Target.Formula) - InStr(Target.Formula, "*"))
                    ElseIf Sh.Name = "Sheet2" Then
                        Sheets("Sheet1").Range(Target.Address).Formula = Left(Sheets("Sheet1").Range(Target.Address).Formula, InStr(Sheets("Sheet1").Range(Target.Address).Formula, "*")) & Right(Target.Formula, Len(Target.Formula) - InStr(Target.Formula, "*"))
                    End If
                End If
            End If
        End If
    End If
End Sub

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Janis Ozols

ASKER
This didn't work for me still changes just C13 cells.
Janis Ozols

ASKER
Ohh wait it worked. thanks very mush for help :)
Janis Ozols

ASKER
What would the Macro be if I could change the (0.97) in C13 and it would automatic change from C13 to I13 in sheet1 and sheet2?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Saqib Husain

Send a sample file.
Janis Ozols

ASKER
Here is the sample file.
aaa.xlsx
ASKER CERTIFIED SOLUTION
Saqib Husain

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Saqib Husain

Assuming the asker's intent.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.