Links Problem

I have multiple sheets in a workbook. I copy a cell from sheet1 to sheet2
and "paste as a link" it works fine. If I edit the pasted formula by
changing the cell reference number the link breaks.

Also, if I try to enter a linked formula directly it works (like ='sheet1'!
$A$12), but if edit it after I enter it stops working.

What am I doing wrong.

I'm using excel 2000 sp 3 (if it matters)

Thank you,

Chris

I don't understand your problem
=sheet1!a1 is fine without spaces in the name. In fact, excel should correct
if you use the '
for sheet names with spaces ='sheet 1'!a1 is required.

--
Don Guillett
SalesAid Software

"chris" wrote in message
6...
I have multiple sheets in a workbook. I copy a cell from sheet1 to sheet2
and "paste as a link" it works fine. If I edit the pasted formula by
changing the cell reference number the link breaks.

Also, if I try to enter a linked formula directly it works (like
='sheet1'!
$A$12), but if edit it after I enter it stops working.

What am I doing wrong.

I'm using excel 2000 sp 3 (if it matters)

Thank you,

Chris

Hi Chris,
What do you mean by "link breaks" and "stops working"? What does the cell display after you edit? What does the formula bar show?

Regards,
Mark Graesser

Boston MA

----- chris wrote: -----

I have multiple sheets in a workbook. I copy a cell from sheet1 to sheet2
and "paste as a link" it works fine. If I edit the pasted formula by
changing the cell reference number the link breaks.

Also, if I try to enter a linked formula directly it works (like ='sheet1'!
$A$12), but if edit it after I enter it stops working.

What am I doing wrong.

I'm using excel 2000 sp 3 (if it matters)

Thank you,

Chris

If I type the formula in as shown and hit enter it returns the value on
the other sheet. If I edit the formula after entering it, the cell ends
up showing the formula I entered.

For example I enter ='sheet1'!$A$11, the cell will show the value from
sheet1, in this case the word Saratoga.

When I edit the formula in the formula bar to ='sheet1'!$A$12 instead of
the value from sheet1 the cell will read ='sheet1'!$A$12.

Also if I look at it in the formula bar it still shows the correct
formula it just no longer works as a link. It's like the cell has chosen
to stop seeing it as a formula and sees it as text only.

And to make things even stranger. On any cell that won't display a link,
due to the above, if I delete the data in the cell and re-enter the
formula it still won't work, but if I paste the formula from another
working linked cell than it will work.

Thank you,

Chris


Hi Chris,
What do you mean by "link breaks" and "stops working"? What does the
cell display after you edit? What does the formula bar show?

Regards,
Mark Graesser

Boston MA

----- chris wrote: -----

I have multiple sheets in a workbook. I copy a cell from sheet1
to sheet2 and "paste as a link" it works fine. If I edit the
pasted formula by changing the cell reference number the link
breaks.

Also, if I try to enter a linked formula directly it works (like
='sheet1'! $A$12), but if edit it after I enter it stops working.

What am I doing wrong.

I'm using excel 2000 sp 3 (if it matters)

Thank you,

Chris

try touching control+tilde (to the left of the 1 on number keys)

--
Don Guillett
SalesAid Software

"chris" wrote in message
6...
If I type the formula in as shown and hit enter it returns the value on
the other sheet. If I edit the formula after entering it, the cell ends
up showing the formula I entered.

For example I enter ='sheet1'!$A$11, the cell will show the value from
sheet1, in this case the word Saratoga.

When I edit the formula in the formula bar to ='sheet1'!$A$12 instead of
the value from sheet1 the cell will read ='sheet1'!$A$12.

Also if I look at it in the formula bar it still shows the correct
formula it just no longer works as a link. It's like the cell has chosen
to stop seeing it as a formula and sees it as text only.

And to make things even stranger. On any cell that won't display a link,
due to the above, if I delete the data in the cell and re-enter the
formula it still won't work, but if I paste the formula from another
working linked cell than it will work.

Thank you,

Chris


Hi Chris,
What do you mean by "link breaks" and "stops working"? What does the
cell display after you edit? What does the formula bar show?

Regards,
Mark Graesser

Boston MA

----- chris wrote: -----

I have multiple sheets in a workbook. I copy a cell from sheet1
to sheet2 and "paste as a link" it works fine. If I edit the
pasted formula by changing the cell reference number the link
breaks.

Also, if I try to enter a linked formula directly it works (like
='sheet1'! $A$12), but if edit it after I enter it stops working.

What am I doing wrong.

I'm using excel 2000 sp 3 (if it matters)

Thank you,

Chris

Hi Chris,
What is the Number format of the cell? If it is set as text, try changing it to general before entering the formula.

Strange problem. As Don pointed out in his first post, the ' ' around sheet1 should be removed by Excel since they are only used on sheet names with a space.

Good Luck,
Mark Graesser

Boston MA
----- chris wrote: -----

If I type the formula in as shown and hit enter it returns the value on
the other sheet. If I edit the formula after entering it, the cell ends
up showing the formula I entered.

For example I enter ='sheet1'!$A$11, the cell will show the value from
sheet1, in this case the word Saratoga.

When I edit the formula in the formula bar to ='sheet1'!$A$12 instead of
the value from sheet1 the cell will read ='sheet1'!$A$12.

Also if I look at it in the formula bar it still shows the correct
formula it just no longer works as a link. It's like the cell has chosen
to stop seeing it as a formula and sees it as text only.

And to make things even stranger. On any cell that won't display a link,
due to the above, if I delete the data in the cell and re-enter the
formula it still won't work, but if I paste the formula from another
working linked cell than it will work.

Thank you,

Chris


Hi Chris,
What do you mean by "link breaks" and "stops working"? What does the
cell display after you edit? What does the formula bar show?
Regards,
Mark Graesser

Boston MA
----- chris wrote: -----
I have multiple sheets in a workbook. I copy a cell from sheet1
to sheet2 and "paste as a link" it works fine. If I edit the
pasted formula by changing the cell reference number the link
breaks.
Also, if I try to enter a linked formula directly it works (like
='sheet1'! $A$12), but if edit it after I enter it stops working.
What am I doing wrong.
I'm using excel 2000 sp 3 (if it matters)
Thank you,
Chris

Make sure you are not inadvertantly adding a space before the = -- that would
change it to text
"chris" wrote in message
6...
: If I type the formula in as shown and hit enter it returns the value on
: the other sheet. If I edit the formula after entering it, the cell ends
: up showing the formula I entered.
:
: For example I enter ='sheet1'!$A$11, the cell will show the value from
: sheet1, in this case the word Saratoga.
:
: When I edit the formula in the formula bar to ='sheet1'!$A$12 instead of
: the value from sheet1 the cell will read ='sheet1'!$A$12.
:
: Also if I look at it in the formula bar it still shows the correct
: formula it just no longer works as a link. It's like the cell has chosen
: to stop seeing it as a formula and sees it as text only.
:
: And to make things even stranger. On any cell that won't display a link,
: due to the above, if I delete the data in the cell and re-enter the
: formula it still won't work, but if I paste the formula from another
: working linked cell than it will work.
:
: Thank you,
:
: Chris
:
:
: Hi Chris,
: What do you mean by "link breaks" and "stops working"? What does the
: cell display after you edit? What does the formula bar show?
:
: Regards,
: Mark Graesser
:
: Boston MA
:
: ----- chris wrote: -----
:
: I have multiple sheets in a workbook. I copy a cell from sheet1
: to sheet2 and "paste as a link" it works fine. If I edit the
: pasted formula by changing the cell reference number the link
: breaks.
:
: Also, if I try to enter a linked formula directly it works (like
: ='sheet1'! $A$12), but if edit it after I enter it stops working.
:
: What am I doing wrong.
:
: I'm using excel 2000 sp 3 (if it matters)
:
: Thank you,
:
: Chris
:
:
:

"Don Guillett" wrote in news:#zi5rM3MEHA.2500
@TK2MSFTNGP12.phx.gbl:

try touching control+tilde (to the left of the 1 on number keys)

That toggles the cell values/formulas and the cells that have the broken
links show the same data either way.

I have tried changing the cell format to just about every choice, but no
difference.

In experimenting more I found I don't even have to edit the cell formula.
All I have to do is put my cursor at the end of the formula and then hit
enter and the link is broken.

But if I put my cursor in the formula bar and then hit escape the link
will remain unchanged and continue to work, but enter kills the link.

It almost seems like there are hidden text codes that are getting
changed, but since they are hidden I can't tell what is happening.

It just doesn't make any sense.

"Don Guillett" wrote in news:#zi5rM3MEHA.2500
@TK2MSFTNGP12.phx.gbl:

try touching control+tilde (to the left of the 1 on number keys)


I now tried the same on a new spreadsheet and all of the link and link
editing works fine. It is only on one specific spreadsheet that this
behaviour is occuring. And even if a copy the sheet to a new workbook
links from that sheet continue to be a problem.

Is there any spreadsheet setting that might cause this kind of thing?

Chris

Hi Chris,
Where did you get this sheet? Maybe there is an event macro that is changing the formatting to text?

Good Luck,
Mark Graesser

Boston MA

----- chris wrote: -----

"Don Guillett" wrote in news:#zi5rM3MEHA.2500
@TK2MSFTNGP12.phx.gbl:

try touching control+tilde (to the left of the 1 on number keys)


I now tried the same on a new spreadsheet and all of the link and link
editing works fine. It is only on one specific spreadsheet that this
behaviour is occuring. And even if a copy the sheet to a new workbook
links from that sheet continue to be a problem.

Is there any spreadsheet setting that might cause this kind of thing?

Chris