Automatic Sheet Updates
 

Hello,

I have a work book with multiple sheets. Some of the sheets have the
same person. I would like (and can't figure out how) to have it so
that when I update it on one page if automatically updates the other
sheets regardless of which sheet I update on.

I have an example that I can send anyone that would like to look at
what I have.

Thank you,

--Jason Coleman

Automatic Sheet Updates
 

On May 30, 4:58 pm, colemanj4 wrote:
Hello,

I have a work book with multiple sheets. Some of the sheets have the
same person. I would like (and can't figure out how) to have it so
that when I update it on one page if automatically updates the other
sheets regardless of which sheet I update on.

I have an example that I can send anyone that would like to look at
what I have.

Thank you,

--Jason Coleman

qwerty

Automatic Sheet Updates
 

For as far I know that's not possible with the Excel Functions.
You could use of course something like the following:

A1: = Sheet2!A1

Probably this isn't what you are looking for because it is just a one-
way reference. What you want is a two way reference.
You could keep it simple by making one sheet master, wherein you make
the changes which will be automatically updated in all the other
linked cells.

Otherwise you could try to write a Macro. You could start with
something like this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If Target.Address =
Workbooks("Book1").Worksheets("Sheet1").Range("She et1Person").Address
Then
Worksheets("Sheet2").Range("Sheet2Person").Value = Target.Value
End If
End Sub

In the Workbook 'Book1' are two sheets with the names 'Sheet1' and
'Sheet2' on which are two defined names called 'Sheet1Person' and
'Sheet2Person'.

Caution is needed. You could make easily an endless loop.

Success,

Bart

Automatic Sheet Updates
 

Hello Bart,

Thanks for the reply. Yea, I thought of that first, but that doesn't
copy back fill across and also there is going to be many different
people editing this book under their repective sheet, so that would
get screwed up pretty quickly.

I am a little confused as to where I should but this and how the
procedure is getting its information (sorry I am used to access where
I can just select an "Event" such as onChange and the code I write
then works when anything on the Object changes.

Other then that it looks like it would work, if I am interpreting it
right (as an aside, is it possible to get backfill for a cell to copy
as well).

If you want, I could attach the book I have (highly simplified from
the original as the original has about 10 sheets and over 400
different names) so that you can better see what I am talking about,
becasue I think I am not being as clear as I need to be.

Thanks,

--Jason Colemna


On May 30, 4:21 pm, Bart wrote:
For as far I know that's not possible with the Excel Functions.
You could use of course something like the following:

A1: = Sheet2!A1

Probably this isn't what you are looking for because it is just a one-
way reference. What you want is a two way reference.
You could keep it simple by making one sheet master, wherein you make
the changes which will be automatically updated in all the other
linked cells.

Otherwise you could try to write a Macro. You could start with
something like this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If Target.Address =
Workbooks("Book1").Worksheets("Sheet1").Range("She et1Person").Address
Then
Worksheets("Sheet2").Range("Sheet2Person").Value = Target.Value
End If
End Sub

In the Workbook 'Book1' are two sheets with the names 'Sheet1' and
'Sheet2' on which are two defined names called 'Sheet1Person' and
'Sheet2Person'.

Caution is needed. You could make easily an endless loop.

Success,

Bart

Automatic Sheet Updates
 

The procedure is part of the ThisWorkbook Object. In the Project
Explorer you can find the ThisWorkbook Object Module. Here you should
put the Subroutine. In this Object Module you can find all the Events
of the Workbook Object, in the Picklist on the upper right (make sure
you picked Workbook in the other picklist left of it first).

Other then that it looks like it would work, if I am interpreting it
right (as an aside, is it possible to get backfill for a cell to copy
as well).

What do you mean with backfill?

Bart