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

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

--

Dave Peterson
(E-Mail Removed)

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
>
> --
>
> Dave Peterson
> (E-Mail Removed)

--

Dave Peterson
(E-Mail Removed)

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





"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
> >
> > --
> >
> > Dave Peterson
> > (E-Mail Removed)
>
> --
>
> Dave Peterson
> (E-Mail Removed)

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

Johnny Eager wrote:
> 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.
>

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.

Johnny Eager wrote:
> 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.
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>
>>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
>>>
>>>--
>>>
>>>Dave Peterson
>>>(E-Mail Removed)
>>
>>--
>>
>>Dave Peterson
>>(E-Mail Removed)
>
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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


"Frank Kabel" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> Johnny Eager wrote:
> > 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 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.

Johnny Eager wrote:
>
> 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
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> > >
> > > --
> > >
> > > Dave Peterson
> > > (E-Mail Removed)
> >
> > --
> >
> > Dave Peterson
> > (E-Mail Removed)

--

Dave Peterson
(E-Mail Removed)

Thank you to all.

I now have a good place to go.

You help is entirely appreciated.

je



"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
> Johnny Eager wrote:
> >
> > 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
> >
> > "Dave Peterson" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > > (E-Mail Removed)
> > >
> > > --
> > >
> > > Dave Peterson
> > > (E-Mail Removed)
>
> --
>
> Dave Peterson
> (E-Mail Removed)

Hi Johnny,

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

Good look with your client :-)
Frank