Updating links to cells in another worksheet

[Cathy C]

Although the cells in worksheet 2 were simultaneously updating when data was
changed when the sheet was first set up, it isn't now. I've checked
Calculations in Options and it's set for Automatic. Any suggestions?

Thanks,
Cathy

 

[Dave Peterson]

Do you really mean worksheets (as worksheets in one workbook) or do you mean a
worksheet in a different workbook?

I'm gonna guess you meant that you had links between two workbooks--I've never
seen xl have trouble with links between worksheets in the same workbook with
calculation set to automatic.

My next guess is that you have two different instances of excel running--and a
workbook open in each instance.

I'm guessing that you don't have one instance open with two workbooks in that
instance.

Try clicking on Window on the worksheet menubar. Do you see both workbook
filenames at the bottom?

If my guesses are correct, you could close one of the instances of excel and do
a File|open in the other to open the second workbook.

If you like to open excel files by double clicking on the filename in windows
explorer, you may want to try this to see if you get that file to open in the
instance of excel that's already running.

Tools|Options|General|Ignore other applications (uncheck it)

--- or ---

Close Excel (both instances) and
Windows Start Button|Run
excel /unregserver
then
Windows Start Button|Run
excel /regserver

The /unregserver & /regserver stuff resets the windows registry to excel's
factory defaults.

 

[Cathy C]

Hi, Dave.

I really mean two worksheets within one workbook and most of it is text.
When we first set it up, it was updating instantaneously. Then when my
client tried to show it to someone else, it wasn't updating. One instance of
Excel running, one workbook. When I click on Window, the one workbook is
shown. We have selected the first three columns of Worksheet A to be
Worksheet B.

Any other thoughts?

Thanks,
Cathy

 

[Dave Peterson]

It's just a simple formula--like:

=sheet1!a1

I don't have a guess--I would check the calculation mode one more time, though.

And you're sure your formulas are still formulas--they haven't been changed to
values?

 

[Dave Peterson]

One more question...

Do you have any user defined functions in your workbook? There could be some
recalculation problems if there's an error in your UDF.

And a second wild guess.

Select the sheet with the troublesome formula.
edit|replace
what: = (equal sign)
with: = (equal sign)

Maybe it'll wake up excel.

 

[Cathy C]

I'll be at the client site tomorrow and will check this out. There's no
equal sign at all on the copy I'm working with. Also, it says [Shared] at
the top. Would this have any effect?

 

[Dave Peterson]

I would guess that the workbook being shared would not affect this kind of
calculation.

Are you saying that there is no equal sign in your formula?

I thought you were updating cells based on a formula like:

=sheet1!a1
(when you're in B99 of sheet2)

If you're not doing this via formulas, are you doing it via macros?

If yes, then maybe you didn't allow macros to run when you opened the
workbook???

I'll be at the client site tomorrow and will check this out. There's no
equal sign at all on the copy I'm working with. Also, it says [Shared] at
the top. Would this have any effect?

 

[Cathy C]

I can't even find the formula now. I was at her office when we created it
and I thought we were in A1 of sheet 2 referencing the first three columns
of sheet 1 when we started, but there's only text there now. Why do you say
B99 of sheet 2?

I would guess that the workbook being shared would not affect this kind of
calculation.

Are you saying that there is no equal sign in your formula?

I thought you were updating cells based on a formula like:

=sheet1!a1
(when you're in B99 of sheet2)

If you're not doing this via formulas, are you doing it via macros?

If yes, then maybe you didn't allow macros to run when you opened the
workbook???

I'll be at the client site tomorrow and will check this out. There's no
equal sign at all on the copy I'm working with. Also, it says [Shared] at
the top. Would this have any effect?

One more question...

Do you have any user defined functions in your workbook? There could
be
some
recalculation problems if there's an error in your UDF.

And a second wild guess.

Select the sheet with the troublesome formula.
edit|replace
what: = (equal sign)
with: = (equal sign)

Maybe it'll wake up excel.

Dave Peterson wrote:

It's just a simple formula--like:

=sheet1!a1

I don't have a guess--I would check the calculation mode one more
time,
though.

And you're sure your formulas are still formulas--they haven't been
changed to
values?

Cathy C wrote:

Hi, Dave.

I really mean two worksheets within one workbook and most of it is
text.
When we first set it up, it was updating instantaneously. Then when
my
client tried to show it to someone else, it wasn't updating. One
instance of
Excel running, one workbook. When I click on Window, the one
workbook
is
shown. We have selected the first three columns of Worksheet A to be
Worksheet B.

Any other thoughts?

Thanks,
Cathy

Do you really mean worksheets (as worksheets in one workbook) or
do
you
mean a
worksheet in a different workbook?

I'm gonna guess you meant that you had links between two
workbooks--I've
never
seen xl have trouble with links between worksheets in the same
workbook
with
calculation set to automatic.

My next guess is that you have two different instances of excel
running--and a
workbook open in each instance.

I'm guessing that you don't have one instance open with two
workbooks
in
that
instance.

Try clicking on Window on the worksheet menubar. Do you see both
workbook
filenames at the bottom?

If my guesses are correct, you could close one of the instances of
excel
and do
a File|open in the other to open the second workbook.

If you like to open excel files by double clicking on the filename
in
windows
explorer, you may want to try this to see if you get that file to
open in
the
instance of excel that's already running.

Tools|Options|General|Ignore other applications (uncheck it)

--- or ---

Close Excel (both instances) and
Windows Start Button|Run
excel /unregserver
then
Windows Start Button|Run
excel /regserver

The /unregserver & /regserver stuff resets the windows registry to
excel's
factory defaults.

Cathy C wrote:

Although the cells in worksheet 2 were simultaneously updating
when
data
was
changed when the sheet was first set up, it isn't now. I've
checked
Calculations in Options and it's set for Automatic. Any
suggestions?

Thanks,
Cathy

 

[Dave Peterson]

It was just an example to show what the formula would look like.

If there's only text there now, you'll have to rebuild the formula.

This works:

=sheet1!a1

until you empty that cell. If A1 is empty, you'll get a 0 back.

I like:

=if(sheet1!a1="","",sheet1!a1)

(in any cell you want on sheet2 <vbg>!)

I can't even find the formula now. I was at her office when we created it
and I thought we were in A1 of sheet 2 referencing the first three columns
of sheet 1 when we started, but there's only text there now. Why do you say
B99 of sheet 2?

I would guess that the workbook being shared would not affect this kind of
calculation.

Are you saying that there is no equal sign in your formula?

I thought you were updating cells based on a formula like:

=sheet1!a1
(when you're in B99 of sheet2)

If you're not doing this via formulas, are you doing it via macros?

If yes, then maybe you didn't allow macros to run when you opened the
workbook???

I'll be at the client site tomorrow and will check this out. There's no
equal sign at all on the copy I'm working with. Also, it says [Shared] at
the top. Would this have any effect?

One more question...

Do you have any user defined functions in your workbook? There could
be
some
recalculation problems if there's an error in your UDF.

And a second wild guess.

Select the sheet with the troublesome formula.
edit|replace
what: = (equal sign)
with: = (equal sign)

Maybe it'll wake up excel.

Dave Peterson wrote:

It's just a simple formula--like:

=sheet1!a1

I don't have a guess--I would check the calculation mode one more
time,
though.

And you're sure your formulas are still formulas--they haven't been
changed to
values?

Cathy C wrote:

Hi, Dave.

I really mean two worksheets within one workbook and most of it is
text.
When we first set it up, it was updating instantaneously. Then when
my
client tried to show it to someone else, it wasn't updating. One
instance of
Excel running, one workbook. When I click on Window, the one
workbook
is
shown. We have selected the first three columns of Worksheet A to be
Worksheet B.

Any other thoughts?

Thanks,
Cathy

Do you really mean worksheets (as worksheets in one workbook) or
do
you
mean a
worksheet in a different workbook?

I'm gonna guess you meant that you had links between two
workbooks--I've
never
seen xl have trouble with links between worksheets in the same
workbook
with
calculation set to automatic.

My next guess is that you have two different instances of excel
running--and a
workbook open in each instance.

I'm guessing that you don't have one instance open with two
workbooks
in
that
instance.

Try clicking on Window on the worksheet menubar. Do you see both
workbook
filenames at the bottom?

If my guesses are correct, you could close one of the instances of
excel
and do
a File|open in the other to open the second workbook.

If you like to open excel files by double clicking on the filename
in
windows
explorer, you may want to try this to see if you get that file to
open in
the
instance of excel that's already running.

Tools|Options|General|Ignore other applications (uncheck it)

--- or ---

Close Excel (both instances) and
Windows Start Button|Run
excel /unregserver
then
Windows Start Button|Run
excel /regserver

The /unregserver & /regserver stuff resets the windows registry to
excel's
factory defaults.

Cathy C wrote:

Although the cells in worksheet 2 were simultaneously updating
when
data
was
changed when the sheet was first set up, it isn't now. I've
checked
Calculations in Options and it's set for Automatic. Any
suggestions?

Thanks,
Cathy

 

[Cathy C]

What about just to get the first three columns in sheet 1?

It was just an example to show what the formula would look like.

If there's only text there now, you'll have to rebuild the formula.

This works:

=sheet1!a1

until you empty that cell. If A1 is empty, you'll get a 0 back.

I like:

=if(sheet1!a1="","",sheet1!a1)

(in any cell you want on sheet2 <vbg>!)

I can't even find the formula now. I was at her office when we created it
and I thought we were in A1 of sheet 2 referencing the first three
columns
of sheet 1 when we started, but there's only text there now. Why do you
say
B99 of sheet 2?

I would guess that the workbook being shared would not affect this kind
of
calculation.

Are you saying that there is no equal sign in your formula?

I thought you were updating cells based on a formula like:

=sheet1!a1
(when you're in B99 of sheet2)

If you're not doing this via formulas, are you doing it via macros?

If yes, then maybe you didn't allow macros to run when you opened the
workbook???



Cathy C wrote:

I'll be at the client site tomorrow and will check this out. There's
no
equal sign at all on the copy I'm working with. Also, it says [Shared]
at
the top. Would this have any effect?

One more question...

Do you have any user defined functions in your workbook? There
could
be
some
recalculation problems if there's an error in your UDF.

And a second wild guess.

Select the sheet with the troublesome formula.
edit|replace
what: = (equal sign)
with: = (equal sign)

Maybe it'll wake up excel.

Dave Peterson wrote:

It's just a simple formula--like:

=sheet1!a1

I don't have a guess--I would check the calculation mode one more
time,
though.

And you're sure your formulas are still formulas--they haven't been
changed to
values?

Cathy C wrote:

Hi, Dave.

I really mean two worksheets within one workbook and most of it
is
text.
When we first set it up, it was updating instantaneously. Then
when
my
client tried to show it to someone else, it wasn't updating. One
instance of
Excel running, one workbook. When I click on Window, the one
workbook
is
shown. We have selected the first three columns of Worksheet A to
be
Worksheet B.

Any other thoughts?

Thanks,
Cathy

Do you really mean worksheets (as worksheets in one workbook)
or
do
you
mean a
worksheet in a different workbook?

I'm gonna guess you meant that you had links between two
workbooks--I've
never
seen xl have trouble with links between worksheets in the same
workbook
with
calculation set to automatic.

My next guess is that you have two different instances of excel
running--and a
workbook open in each instance.

I'm guessing that you don't have one instance open with two
workbooks
in
that
instance.

Try clicking on Window on the worksheet menubar. Do you see
both
workbook
filenames at the bottom?

If my guesses are correct, you could close one of the instances
of
excel
and do
a File|open in the other to open the second workbook.

If you like to open excel files by double clicking on the
filename
in
windows
explorer, you may want to try this to see if you get that file
to
open in
the
instance of excel that's already running.

Tools|Options|General|Ignore other applications (uncheck it)

--- or ---

Close Excel (both instances) and
Windows Start Button|Run
excel /unregserver
then
Windows Start Button|Run
excel /regserver

The /unregserver & /regserver stuff resets the windows registry
to
excel's
factory defaults.

Cathy C wrote:

Although the cells in worksheet 2 were simultaneously updating
when
data
was
changed when the sheet was first set up, it isn't now. I've
checked
Calculations in Options and it's set for Automatic. Any
suggestions?

Thanks,
Cathy

 

[Dave Peterson]

You want all the cells or just the first row?

In A1 of the second sheet:
=if(sheet1!a1="","",sheet1!a1)

And copy to the right a couple of columns. If you want more rows, just copy it
down until you're happy.

What about just to get the first three columns in sheet 1?

It was just an example to show what the formula would look like.

If there's only text there now, you'll have to rebuild the formula.

This works:

=sheet1!a1

until you empty that cell. If A1 is empty, you'll get a 0 back.

I like:

=if(sheet1!a1="","",sheet1!a1)

(in any cell you want on sheet2 <vbg>!)

I can't even find the formula now. I was at her office when we created it
and I thought we were in A1 of sheet 2 referencing the first three
columns
of sheet 1 when we started, but there's only text there now. Why do you
say
B99 of sheet 2?

I would guess that the workbook being shared would not affect this kind
of
calculation.

Are you saying that there is no equal sign in your formula?

I thought you were updating cells based on a formula like:

=sheet1!a1
(when you're in B99 of sheet2)

If you're not doing this via formulas, are you doing it via macros?

If yes, then maybe you didn't allow macros to run when you opened the
workbook???



Cathy C wrote:

I'll be at the client site tomorrow and will check this out. There's
no
equal sign at all on the copy I'm working with. Also, it says [Shared]
at
the top. Would this have any effect?

One more question...

Do you have any user defined functions in your workbook? There
could
be
some
recalculation problems if there's an error in your UDF.

And a second wild guess.

Select the sheet with the troublesome formula.
edit|replace
what: = (equal sign)
with: = (equal sign)

Maybe it'll wake up excel.

Dave Peterson wrote:

It's just a simple formula--like:

=sheet1!a1

I don't have a guess--I would check the calculation mode one more
time,
though.

And you're sure your formulas are still formulas--they haven't been
changed to
values?

Cathy C wrote:

Hi, Dave.

I really mean two worksheets within one workbook and most of it
is
text.
When we first set it up, it was updating instantaneously. Then
when
my
client tried to show it to someone else, it wasn't updating. One
instance of
Excel running, one workbook. When I click on Window, the one
workbook
is
shown. We have selected the first three columns of Worksheet A to
be
Worksheet B.

Any other thoughts?

Thanks,
Cathy

Do you really mean worksheets (as worksheets in one workbook)
or
do
you
mean a
worksheet in a different workbook?

I'm gonna guess you meant that you had links between two
workbooks--I've
never
seen xl have trouble with links between worksheets in the same
workbook
with
calculation set to automatic.

My next guess is that you have two different instances of excel
running--and a
workbook open in each instance.

I'm guessing that you don't have one instance open with two
workbooks
in
that
instance.

Try clicking on Window on the worksheet menubar. Do you see
both
workbook
filenames at the bottom?

If my guesses are correct, you could close one of the instances
of
excel
and do
a File|open in the other to open the second workbook.

If you like to open excel files by double clicking on the
filename
in
windows
explorer, you may want to try this to see if you get that file
to
open in
the
instance of excel that's already running.

Tools|Options|General|Ignore other applications (uncheck it)

--- or ---

Close Excel (both instances) and
Windows Start Button|Run
excel /unregserver
then
Windows Start Button|Run
excel /regserver

The /unregserver & /regserver stuff resets the windows registry
to
excel's
factory defaults.

Cathy C wrote:

Although the cells in worksheet 2 were simultaneously updating
when
data
was
changed when the sheet was first set up, it isn't now. I've
checked
Calculations in Options and it's set for Automatic. Any
suggestions?

Thanks,
Cathy

 

[Cathy C]

Well, here's what we did that seems to work:


How to link pages in Excel. Link information from one worksheet to another
so updating information only has to be keyed in once:
Go into the worksheet containing the data
Select both worksheets
Select columns to past
Edit
Fill
Across worksheets
NOTE: After both pages are set up, in order for any changes on the first
(data) sheet to also change on the new sheet, both worksheets MUST be
selected.

Do you think there's an advantage to the way you describe below? (She did
want all rows of the first three columns.)

Thanks,
C

You want all the cells or just the first row?

In A1 of the second sheet:
=if(sheet1!a1="","",sheet1!a1)

And copy to the right a couple of columns. If you want more rows, just
copy it
down until you're happy.

What about just to get the first three columns in sheet 1?

It was just an example to show what the formula would look like.

If there's only text there now, you'll have to rebuild the formula.

This works:

=sheet1!a1

until you empty that cell. If A1 is empty, you'll get a 0 back.

I like:

=if(sheet1!a1="","",sheet1!a1)

(in any cell you want on sheet2 <vbg>!)

Cathy C wrote:

I can't even find the formula now. I was at her office when we created
it
and I thought we were in A1 of sheet 2 referencing the first three
columns
of sheet 1 when we started, but there's only text there now. Why do
you
say
B99 of sheet 2?

I would guess that the workbook being shared would not affect this
kind
of
calculation.

Are you saying that there is no equal sign in your formula?

I thought you were updating cells based on a formula like:

=sheet1!a1
(when you're in B99 of sheet2)

If you're not doing this via formulas, are you doing it via macros?

If yes, then maybe you didn't allow macros to run when you opened
the
workbook???



Cathy C wrote:

I'll be at the client site tomorrow and will check this out.
There's
no
equal sign at all on the copy I'm working with. Also, it says
[Shared]
at
the top. Would this have any effect?

One more question...

Do you have any user defined functions in your workbook? There
could
be
some
recalculation problems if there's an error in your UDF.

And a second wild guess.

Select the sheet with the troublesome formula.
edit|replace
what: = (equal sign)
with: = (equal sign)

Maybe it'll wake up excel.

Dave Peterson wrote:

It's just a simple formula--like:

=sheet1!a1

I don't have a guess--I would check the calculation mode one
more
time,
though.

And you're sure your formulas are still formulas--they haven't
been
changed to
values?

Cathy C wrote:

Hi, Dave.

I really mean two worksheets within one workbook and most of
it
is
text.
When we first set it up, it was updating instantaneously. Then
when
my
client tried to show it to someone else, it wasn't updating.
One
instance of
Excel running, one workbook. When I click on Window, the one
workbook
is
shown. We have selected the first three columns of Worksheet A
to
be
Worksheet B.

Any other thoughts?

Thanks,
Cathy

Do you really mean worksheets (as worksheets in one
workbook)
or
do
you
mean a
worksheet in a different workbook?

I'm gonna guess you meant that you had links between two
workbooks--I've
never
seen xl have trouble with links between worksheets in the
same
workbook
with
calculation set to automatic.

My next guess is that you have two different instances of
excel
running--and a
workbook open in each instance.

I'm guessing that you don't have one instance open with two
workbooks
in
that
instance.

Try clicking on Window on the worksheet menubar. Do you see
both
workbook
filenames at the bottom?

If my guesses are correct, you could close one of the
instances
of
excel
and do
a File|open in the other to open the second workbook.

If you like to open excel files by double clicking on the
filename
in
windows
explorer, you may want to try this to see if you get that
file
to
open in
the
instance of excel that's already running.

Tools|Options|General|Ignore other applications (uncheck it)

--- or ---

Close Excel (both instances) and
Windows Start Button|Run
excel /unregserver
then
Windows Start Button|Run
excel /regserver

The /unregserver & /regserver stuff resets the windows
registry
to
excel's
factory defaults.

Cathy C wrote:

Although the cells in worksheet 2 were simultaneously
updating
when
data
was
changed when the sheet was first set up, it isn't now. I've
checked
Calculations in Options and it's set for Automatic. Any
suggestions?

Thanks,
Cathy

 

[Dave Peterson]

The advantage of using a formula (like I did) is that you don't have to group
sheets first.

When you group sheets any change you make to one of the grouped sheets will show
up in the others. You group your sheets, then fix what you want to fix--but
forget to ungroup, and then make more changes, it could be a serious problem.

But if you don't forget, then not a problem at all--and xl tries to warn you by
putting [Group] in the title bar. But I only do this when I'm initially setting
up similar worksheets. And I do my best not to forget to ungroup.

What's nice about the formula approach is that you change the original data, and
all the cells that point back to it are updated. The semi-bad thing is you
can't change formats and have the formulas reflect those changes.

So if you change to a bold/red/22 pitch font, you'll have to do that some other
way (grouping is ok).

===
My personal opinion: I like the formula approach. Change it once and forget
about it. But that's just me.

Well, here's what we did that seems to work:

How to link pages in Excel. Link information from one worksheet to another
so updating information only has to be keyed in once:
Go into the worksheet containing the data
Select both worksheets
Select columns to past
Edit
Fill
Across worksheets
NOTE: After both pages are set up, in order for any changes on the first
(data) sheet to also change on the new sheet, both worksheets MUST be
selected.

Do you think there's an advantage to the way you describe below? (She did
want all rows of the first three columns.)

Thanks,
C

You want all the cells or just the first row?

In A1 of the second sheet:
=if(sheet1!a1="","",sheet1!a1)

And copy to the right a couple of columns. If you want more rows, just
copy it
down until you're happy.

What about just to get the first three columns in sheet 1?

It was just an example to show what the formula would look like.

If there's only text there now, you'll have to rebuild the formula.

This works:

=sheet1!a1

until you empty that cell. If A1 is empty, you'll get a 0 back.

I like:

=if(sheet1!a1="","",sheet1!a1)

(in any cell you want on sheet2 <vbg>!)

Cathy C wrote:

I can't even find the formula now. I was at her office when we created
it
and I thought we were in A1 of sheet 2 referencing the first three
columns
of sheet 1 when we started, but there's only text there now. Why do
you
say
B99 of sheet 2?

I would guess that the workbook being shared would not affect this
kind
of
calculation.

Are you saying that there is no equal sign in your formula?

I thought you were updating cells based on a formula like:

=sheet1!a1
(when you're in B99 of sheet2)

If you're not doing this via formulas, are you doing it via macros?

If yes, then maybe you didn't allow macros to run when you opened
the
workbook???



Cathy C wrote:

I'll be at the client site tomorrow and will check this out.
There's
no
equal sign at all on the copy I'm working with. Also, it says
[Shared]
at
the top. Would this have any effect?

One more question...

Do you have any user defined functions in your workbook? There
could
be
some
recalculation problems if there's an error in your UDF.

And a second wild guess.

Select the sheet with the troublesome formula.
edit|replace
what: = (equal sign)
with: = (equal sign)

Maybe it'll wake up excel.

Dave Peterson wrote:

It's just a simple formula--like:

=sheet1!a1

I don't have a guess--I would check the calculation mode one
more
time,
though.

And you're sure your formulas are still formulas--they haven't
been
changed to
values?

Cathy C wrote:

Hi, Dave.

I really mean two worksheets within one workbook and most of
it
is
text.
When we first set it up, it was updating instantaneously. Then
when
my
client tried to show it to someone else, it wasn't updating.
One
instance of
Excel running, one workbook. When I click on Window, the one
workbook
is
shown. We have selected the first three columns of Worksheet A
to
be
Worksheet B.

Any other thoughts?

Thanks,
Cathy

Do you really mean worksheets (as worksheets in one
workbook)
or
do
you
mean a
worksheet in a different workbook?

I'm gonna guess you meant that you had links between two
workbooks--I've
never
seen xl have trouble with links between worksheets in the
same
workbook
with
calculation set to automatic.

My next guess is that you have two different instances of
excel
running--and a
workbook open in each instance.

I'm guessing that you don't have one instance open with two
workbooks
in
that
instance.

Try clicking on Window on the worksheet menubar. Do you see
both
workbook
filenames at the bottom?

If my guesses are correct, you could close one of the
instances
of
excel
and do
a File|open in the other to open the second workbook.

If you like to open excel files by double clicking on the
filename
in
windows
explorer, you may want to try this to see if you get that
file
to
open in
the
instance of excel that's already running.

Tools|Options|General|Ignore other applications (uncheck it)

--- or ---

Close Excel (both instances) and
Windows Start Button|Run
excel /unregserver
then
Windows Start Button|Run
excel /regserver

The /unregserver & /regserver stuff resets the windows
registry
to
excel's
factory defaults.

Cathy C wrote:

Although the cells in worksheet 2 were simultaneously
updating
when
data
was
changed when the sheet was first set up, it isn't now. I've
checked
Calculations in Options and it's set for Automatic. Any
suggestions?

Thanks,
Cathy

 

[Cathy C]

OK. Thanks much, Dave. I'll pass this information on to my client.

C

The advantage of using a formula (like I did) is that you don't have to
group
sheets first.

When you group sheets any change you make to one of the grouped sheets
will show
up in the others. You group your sheets, then fix what you want to
fix--but
forget to ungroup, and then make more changes, it could be a serious
problem.

But if you don't forget, then not a problem at all--and xl tries to warn
you by
putting [Group] in the title bar. But I only do this when I'm initially
setting
up similar worksheets. And I do my best not to forget to ungroup.

What's nice about the formula approach is that you change the original
data, and
all the cells that point back to it are updated. The semi-bad thing is
you
can't change formats and have the formulas reflect those changes.

So if you change to a bold/red/22 pitch font, you'll have to do that some
other
way (grouping is ok).

===
My personal opinion: I like the formula approach. Change it once and
forget
about it. But that's just me.

Well, here's what we did that seems to work:

How to link pages in Excel. Link information from one worksheet to
another
so updating information only has to be keyed in once:
Go into the worksheet containing the data
Select both worksheets
Select columns to past
Edit
Fill
Across worksheets
NOTE: After both pages are set up, in order for any changes on the first
(data) sheet to also change on the new sheet, both worksheets MUST be
selected.

Do you think there's an advantage to the way you describe below? (She did
want all rows of the first three columns.)

Thanks,
C

You want all the cells or just the first row?

In A1 of the second sheet:
=if(sheet1!a1="","",sheet1!a1)

And copy to the right a couple of columns. If you want more rows, just
copy it
down until you're happy.

Cathy C wrote:

What about just to get the first three columns in sheet 1?

It was just an example to show what the formula would look like.

If there's only text there now, you'll have to rebuild the formula.

This works:

=sheet1!a1

until you empty that cell. If A1 is empty, you'll get a 0 back.

I like:

=if(sheet1!a1="","",sheet1!a1)

(in any cell you want on sheet2 <vbg>!)

Cathy C wrote:

I can't even find the formula now. I was at her office when we
created
it
and I thought we were in A1 of sheet 2 referencing the first three
columns
of sheet 1 when we started, but there's only text there now. Why do
you
say
B99 of sheet 2?

I would guess that the workbook being shared would not affect this
kind
of
calculation.

Are you saying that there is no equal sign in your formula?

I thought you were updating cells based on a formula like:

=sheet1!a1
(when you're in B99 of sheet2)

If you're not doing this via formulas, are you doing it via
macros?

If yes, then maybe you didn't allow macros to run when you opened
the
workbook???



Cathy C wrote:

I'll be at the client site tomorrow and will check this out.
There's
no
equal sign at all on the copy I'm working with. Also, it says
[Shared]
at
the top. Would this have any effect?

One more question...

Do you have any user defined functions in your workbook?
There
could
be
some
recalculation problems if there's an error in your UDF.

And a second wild guess.

Select the sheet with the troublesome formula.
edit|replace
what: = (equal sign)
with: = (equal sign)

Maybe it'll wake up excel.

Dave Peterson wrote:

It's just a simple formula--like:

=sheet1!a1

I don't have a guess--I would check the calculation mode one
more
time,
though.

And you're sure your formulas are still formulas--they
haven't
been
changed to
values?

Cathy C wrote:

Hi, Dave.

I really mean two worksheets within one workbook and most
of
it
is
text.
When we first set it up, it was updating instantaneously.
Then
when
my
client tried to show it to someone else, it wasn't
updating.
One
instance of
Excel running, one workbook. When I click on Window, the
one
workbook
is
shown. We have selected the first three columns of
Worksheet A
to
be
Worksheet B.

Any other thoughts?

Thanks,
Cathy

Do you really mean worksheets (as worksheets in one
workbook)
or
do
you
mean a
worksheet in a different workbook?

I'm gonna guess you meant that you had links between two
workbooks--I've
never
seen xl have trouble with links between worksheets in the
same
workbook
with
calculation set to automatic.

My next guess is that you have two different instances of
excel
running--and a
workbook open in each instance.

I'm guessing that you don't have one instance open with
two
workbooks
in
that
instance.

Try clicking on Window on the worksheet menubar. Do you
see
both
workbook
filenames at the bottom?

If my guesses are correct, you could close one of the
instances
of
excel
and do
a File|open in the other to open the second workbook.

If you like to open excel files by double clicking on the
filename
in
windows
explorer, you may want to try this to see if you get that
file
to
open in
the
instance of excel that's already running.

Tools|Options|General|Ignore other applications (uncheck
it)

--- or ---

Close Excel (both instances) and
Windows Start Button|Run
excel /unregserver
then
Windows Start Button|Run
excel /regserver

The /unregserver & /regserver stuff resets the windows
registry
to
excel's
factory defaults.

Cathy C wrote:

Although the cells in worksheet 2 were simultaneously
updating
when
data
was
changed when the sheet was first set up, it isn't now.
I've
checked
Calculations in Options and it's set for Automatic. Any
suggestions?

Thanks,
Cathy