creating dymanic mirrored worksheets

[Johnny Eager]

I have project where I import (via Word Automation) text content into a
Excel template. All text, no calculations.

I want to have a second worksheet which mirrors certain columns from sheet
1. Here is the problem.

How can I have both worksheets dynamic at the same time?

If I enter data in worksheet one, I want the data to appear in the same cell
row (like-named column). This I can do.

But what want is to have worksheet 2 also dynamic so when I enter data into
a row, it appears in the like-named column on worksheet 1.

In other words, I want to be able to enter data in either sheet, and the
results are the same in both.

thanks for the help.

JE

 

[Dave Peterson]

If I had to do this, I think I'd have to open both workbooks (so one could
update the other) and have code that looked for changes in each and then updated
the other.

The bad news this is really easy to screw up. If you don't have both workbooks
open. If macros are disabled, if events are disabled.

I think you'll find life much better if you decide one worksheet is the master
and the other is a mirror.

You can use a formula like:

=IF([book1.xls]Sheet1!A1="","",[book1.xls]Sheet1!A1)

in the mirrored file.

Another alternative would be to just retrieve a copy of the first worksheet
whenever you need a fresh version.

 

[Dave Peterson]

I was just notified by the CQC that your worksheets are in the same workbook--I
mistakenly thought that they were in different workbooks.

Even with that change, I don't think I'd do it. Let one worksheet be the parent
and let one be the child.

If I had to do this, I think I'd have to open both workbooks (so one could
update the other) and have code that looked for changes in each and then updated
the other.

The bad news this is really easy to screw up. If you don't have both workbooks
open. If macros are disabled, if events are disabled.

I think you'll find life much better if you decide one worksheet is the master
and the other is a mirror.

You can use a formula like:

=IF([book1.xls]Sheet1!A1="","",[book1.xls]Sheet1!A1)

in the mirrored file.

Another alternative would be to just retrieve a copy of the first worksheet
whenever you need a fresh version.

I have project where I import (via Word Automation) text content into a
Excel template. All text, no calculations.

I want to have a second worksheet which mirrors certain columns from sheet
1. Here is the problem.

How can I have both worksheets dynamic at the same time?

If I enter data in worksheet one, I want the data to appear in the same cell
row (like-named column). This I can do.

But what want is to have worksheet 2 also dynamic so when I enter data into
a row, it appears in the like-named column on worksheet 1.

In other words, I want to be able to enter data in either sheet, and the
results are the same in both.

thanks for the help.

JE

 

[Johnny Eager]

Dave,

Thanks for the input. I tried to convince them to keep one sheet as the
master, but they insist two different departments will use these sheets and
one department wants all the fields, and the other only wants a few.

Is there a way to envoke a macro in an installed add-in that will refresh
the spreadsheet upon becoming active?

I guess I could even have a macro button that they have to click after data
input on the child sheet. If that were the case, is there a method to
repopulate only certain columns in one worksheet from another.

thank you so much for the help. I felt I was in Excel Purgatory and was only
one sin away from a long fall.

je

I was just notified by the CQC that your worksheets are in the same workbook--I
mistakenly thought that they were in different workbooks.

Even with that change, I don't think I'd do it. Let one worksheet be the parent
and let one be the child.

If I had to do this, I think I'd have to open both workbooks (so one could
update the other) and have code that looked for changes in each and then updated
the other.

The bad news this is really easy to screw up. If you don't have both workbooks
open. If macros are disabled, if events are disabled.

I think you'll find life much better if you decide one worksheet is the master
and the other is a mirror.

You can use a formula like:

=IF([book1.xls]Sheet1!A1="","",[book1.xls]Sheet1!A1)

in the mirrored file.

Another alternative would be to just retrieve a copy of the first worksheet
whenever you need a fresh version.

I have project where I import (via Word Automation) text content into a
Excel template. All text, no calculations.

I want to have a second worksheet which mirrors certain columns from sheet
1. Here is the problem.

How can I have both worksheets dynamic at the same time?

If I enter data in worksheet one, I want the data to appear in the same cell
row (like-named column). This I can do.

But what want is to have worksheet 2 also dynamic so when I enter data into
a row, it appears in the like-named column on worksheet 1.

In other words, I want to be able to enter data in either sheet, and the
results are the same in both.

thanks for the help.

JE

 

[Frank Kabel]

Hi Johnny
though i'm not Dave some comments from my side to your problem
1. As Dave mentioned 'double-mirroring' is more or less a bad idea and
not supported by Excel. You would need Event´macros
2. According to your description two different departments (and
therefore at least two different users) will use the sheets. IMO Excel
would not be the tool of choice to ensure multi-user interaction (e.g.
what will happen, if at the same time, the same cell is changed by two
users, etc.). This kind of application is better suited in a database
(then you can lock entries, etc.). For reporting purposes there is no
problem to export these data to Excel.

Frank

 

[Debra Dalgleish]

You could create custom views (View>Custom Views) that show or hide the
columns required by each department. They could both use the same sheet,
and view only the columns that they need.

Dave,

Thanks for the input. I tried to convince them to keep one sheet as the
master, but they insist two different departments will use these sheets and
one department wants all the fields, and the other only wants a few.

Is there a way to envoke a macro in an installed add-in that will refresh
the spreadsheet upon becoming active?

I guess I could even have a macro button that they have to click after data
input on the child sheet. If that were the case, is there a method to
repopulate only certain columns in one worksheet from another.

thank you so much for the help. I felt I was in Excel Purgatory and was only
one sin away from a long fall.

I was just notified by the CQC that your worksheets are in the same
workbook--I

mistakenly thought that they were in different workbooks.

Even with that change, I don't think I'd do it. Let one worksheet be the
parent

and let one be the child.

If I had to do this, I think I'd have to open both workbooks (so one

could

update the other) and have code that looked for changes in each and then

updated

the other.

The bad news this is really easy to screw up. If you don't have both

workbooks

open. If macros are disabled, if events are disabled.

I think you'll find life much better if you decide one worksheet is the

master

and the other is a mirror.

You can use a formula like:

=IF([book1.xls]Sheet1!A1="","",[book1.xls]Sheet1!A1)

in the mirrored file.

Another alternative would be to just retrieve a copy of the first

worksheet

whenever you need a fresh version.

Johnny Eager wrote:

I have project where I import (via Word Automation) text content into

a

Excel template. All text, no calculations.

I want to have a second worksheet which mirrors certain columns from

sheet

1. Here is the problem.

How can I have both worksheets dynamic at the same time?

If I enter data in worksheet one, I want the data to appear in the

same cell

 

[Johnny Eager]

Frank,

Your comments are well taken. Thank you. Sometimes trying to please too many
only makes a mess. I will rethink my approach to them.

I can see why they want this second worksheet, however. They want their data
entry people to use the smaller more manageable worksheet instead of having
to scroll through the all the columns. But the integrity of the data is more
important than convenience.

Thanks again,

je

 

[Dave Peterson]

I don't use custom views very often (almost never).

But I do use Data|Filter|Autofilter to hide/show rows.

And I use macros to hide/unhide columns.

(You could even group your columns by using Data|Group & outline|Group.)

I find the macros easier--probably because I don't use Views! <vbg>.

Try Debra Dalgleish's suggestion of View|Custom View and if you like that, use
it.

If you want to try a macro, record one while you do it manually and post back if
you need help.

Dave,

Thanks for the input. I tried to convince them to keep one sheet as the
master, but they insist two different departments will use these sheets and
one department wants all the fields, and the other only wants a few.

Is there a way to envoke a macro in an installed add-in that will refresh
the spreadsheet upon becoming active?

I guess I could even have a macro button that they have to click after data
input on the child sheet. If that were the case, is there a method to
repopulate only certain columns in one worksheet from another.

thank you so much for the help. I felt I was in Excel Purgatory and was only
one sin away from a long fall.

je

I was just notified by the CQC that your worksheets are in the same workbook--I
mistakenly thought that they were in different workbooks.

Even with that change, I don't think I'd do it. Let one worksheet be the parent
and let one be the child.

If I had to do this, I think I'd have to open both workbooks (so one could
update the other) and have code that looked for changes in each and then updated
the other.

The bad news this is really easy to screw up. If you don't have both workbooks
open. If macros are disabled, if events are disabled.

I think you'll find life much better if you decide one worksheet is the master
and the other is a mirror.

You can use a formula like:

=IF([book1.xls]Sheet1!A1="","",[book1.xls]Sheet1!A1)

in the mirrored file.

Another alternative would be to just retrieve a copy of the first worksheet
whenever you need a fresh version.

Johnny Eager wrote:

I have project where I import (via Word Automation) text content into a
Excel template. All text, no calculations.

I want to have a second worksheet which mirrors certain columns from sheet
1. Here is the problem.

How can I have both worksheets dynamic at the same time?

If I enter data in worksheet one, I want the data to appear in the same cell
row (like-named column). This I can do.

But what want is to have worksheet 2 also dynamic so when I enter data into
a row, it appears in the like-named column on worksheet 1.

In other words, I want to be able to enter data in either sheet, and the
results are the same in both.

thanks for the help.

JE

 

[Johnny Eager]

Thank you to all.

I now have a good place to go.

You help is entirely appreciated.

je

I don't use custom views very often (almost never).

But I do use Data|Filter|Autofilter to hide/show rows.

And I use macros to hide/unhide columns.

(You could even group your columns by using Data|Group & outline|Group.)

I find the macros easier--probably because I don't use Views! <vbg>.

Try Debra Dalgleish's suggestion of View|Custom View and if you like that, use
it.

If you want to try a macro, record one while you do it manually and post back if
you need help.

Dave,

Thanks for the input. I tried to convince them to keep one sheet as the
master, but they insist two different departments will use these sheets and
one department wants all the fields, and the other only wants a few.

Is there a way to envoke a macro in an installed add-in that will refresh
the spreadsheet upon becoming active?

I guess I could even have a macro button that they have to click after data
input on the child sheet. If that were the case, is there a method to
repopulate only certain columns in one worksheet from another.

thank you so much for the help. I felt I was in Excel Purgatory and was only
one sin away from a long fall.

je

I was just notified by the CQC that your worksheets are in the same workbook--I
mistakenly thought that they were in different workbooks.

Even with that change, I don't think I'd do it. Let one worksheet be

the
parent

and let one be the child.



Dave Peterson wrote:

If I had to do this, I think I'd have to open both workbooks (so one could
update the other) and have code that looked for changes in each and

then
updated

the other.

The bad news this is really easy to screw up. If you don't have

both
workbooks

open. If macros are disabled, if events are disabled.

I think you'll find life much better if you decide one worksheet is

the
master

and the other is a mirror.

You can use a formula like:

=IF([book1.xls]Sheet1!A1="","",[book1.xls]Sheet1!A1)

in the mirrored file.

Another alternative would be to just retrieve a copy of the first worksheet
whenever you need a fresh version.

Johnny Eager wrote:

I have project where I import (via Word Automation) text content

into
a

Excel template. All text, no calculations.

I want to have a second worksheet which mirrors certain columns

from
sheet

1. Here is the problem.

How can I have both worksheets dynamic at the same time?

If I enter data in worksheet one, I want the data to appear in the same cell
row (like-named column). This I can do.

But what want is to have worksheet 2 also dynamic so when I enter

data
into

a row, it appears in the like-named column on worksheet 1.

In other words, I want to be able to enter data in either sheet,

and
the

results are the same in both.

thanks for the help.

JE

 

[Frank Kabel]

Hi Johnny,

then i think Dave and Debra have some good points/suggestions to start
from

Good look with your client
Frank