Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default Problem Updating Links

I have a workbook (created by someone else...ugh) that uses links to another
workbook (an answer sheet), no longer available. I have a new version of the
answer sheet, but when I try to edit the cells in the first workbook, they
will only display the formula itself of the actual link.

e.g., the cell displays: ='[ITEMS for MC TABLE REV1.xls]ITEMS SORTED'!$F$12

Nothing short of Edit/Clear/Formats seems to let me redo these links. Oh, it
lets me change the link, but then simply displays the link as text, just as
it did the previous link. Also, I can't even add a new link anywhere on the
worksheet, until I clear the formats. One clue might be this: the cells in
question are set to Text format, as the text is a decimal number (such as
415.99). I've tried just changing the cell's Number format to something else,
but no joy. I've also tried unprotecting cells, the sheet, unlocking cells,
etc. No joy.

Any ideas?
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Problem Updating Links

Have you tried changing the cell to General (or Number)
Then select that cell and hit F2 followed by enter.

Changing the format of a cell isn't enough. You have to reenter the
formula--Hitting F2, then enter does that.

If you have lots of these cells to fix.

Select the range to fix
format|cells|number tab|general (or anything but Text)
And then
Edit|replace
what: = (equal sign)
with: = (the same equal sign)
replace all

Changing the equal sign will tell excel that you "changed" the formula and excel
will reevaluate each of those cells.

Delos wrote:

I have a workbook (created by someone else...ugh) that uses links to another
workbook (an answer sheet), no longer available. I have a new version of the
answer sheet, but when I try to edit the cells in the first workbook, they
will only display the formula itself of the actual link.

e.g., the cell displays: ='[ITEMS for MC TABLE REV1.xls]ITEMS SORTED'!$F$12

Nothing short of Edit/Clear/Formats seems to let me redo these links. Oh, it
lets me change the link, but then simply displays the link as text, just as
it did the previous link. Also, I can't even add a new link anywhere on the
worksheet, until I clear the formats. One clue might be this: the cells in
question are set to Text format, as the text is a decimal number (such as
415.99). I've tried just changing the cell's Number format to something else,
but no joy. I've also tried unprotecting cells, the sheet, unlocking cells,
etc. No joy.

Any ideas?


--

Dave Peterson
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default Problem Updating Links

Tried that, Dave, no luck. the F2 trick simply converts the display of the
cell from the original number to the text formula display. And the replace
command opens a file selection box for each cell, but never makes a change in
the formula.

thanks for trying.

"Dave Peterson" wrote:

Have you tried changing the cell to General (or Number)
Then select that cell and hit F2 followed by enter.

Changing the format of a cell isn't enough. You have to reenter the
formula--Hitting F2, then enter does that.

If you have lots of these cells to fix.

Select the range to fix
format|cells|number tab|general (or anything but Text)
And then
Edit|replace
what: = (equal sign)
with: = (the same equal sign)
replace all

Changing the equal sign will tell excel that you "changed" the formula and excel
will reevaluate each of those cells.

Delos wrote:

I have a workbook (created by someone else...ugh) that uses links to another
workbook (an answer sheet), no longer available. I have a new version of the
answer sheet, but when I try to edit the cells in the first workbook, they
will only display the formula itself of the actual link.

e.g., the cell displays: ='[ITEMS for MC TABLE REV1.xls]ITEMS SORTED'!$F$12

Nothing short of Edit/Clear/Formats seems to let me redo these links. Oh, it
lets me change the link, but then simply displays the link as text, just as
it did the previous link. Also, I can't even add a new link anywhere on the
worksheet, until I clear the formats. One clue might be this: the cells in
question are set to Text format, as the text is a decimal number (such as
415.99). I've tried just changing the cell's Number format to something else,
but no joy. I've also tried unprotecting cells, the sheet, unlocking cells,
etc. No joy.

Any ideas?


--

Dave Peterson

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Problem Updating Links

Your formula expects that "sending" file to be open. If it's not, you'll get
prompted to locate it.

You can either open that other workbook, then do those steps--or browse for that
workbook.

And I don't understand what you mean about not changing the formula. When you
started, it wasn't a formula--it was plain old text.

After you finished, the text was converted to a formula (since you were prompted
for the location of the other workbook).

Delos wrote:

Tried that, Dave, no luck. the F2 trick simply converts the display of the
cell from the original number to the text formula display. And the replace
command opens a file selection box for each cell, but never makes a change in
the formula.

thanks for trying.

"Dave Peterson" wrote:

Have you tried changing the cell to General (or Number)
Then select that cell and hit F2 followed by enter.

Changing the format of a cell isn't enough. You have to reenter the
formula--Hitting F2, then enter does that.

If you have lots of these cells to fix.

Select the range to fix
format|cells|number tab|general (or anything but Text)
And then
Edit|replace
what: = (equal sign)
with: = (the same equal sign)
replace all

Changing the equal sign will tell excel that you "changed" the formula and excel
will reevaluate each of those cells.

Delos wrote:

I have a workbook (created by someone else...ugh) that uses links to another
workbook (an answer sheet), no longer available. I have a new version of the
answer sheet, but when I try to edit the cells in the first workbook, they
will only display the formula itself of the actual link.

e.g., the cell displays: ='[ITEMS for MC TABLE REV1.xls]ITEMS SORTED'!$F$12

Nothing short of Edit/Clear/Formats seems to let me redo these links. Oh, it
lets me change the link, but then simply displays the link as text, just as
it did the previous link. Also, I can't even add a new link anywhere on the
worksheet, until I clear the formats. One clue might be this: the cells in
question are set to Text format, as the text is a decimal number (such as
415.99). I've tried just changing the cell's Number format to something else,
but no joy. I've also tried unprotecting cells, the sheet, unlocking cells,
etc. No joy.

Any ideas?


--

Dave Peterson


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Links are not updating S.Latza Excel Worksheet Functions 0 April 18th 08 07:47 PM
problem updating links unless sources are open Sue Excel Worksheet Functions 1 June 4th 07 03:53 PM
Problem with updating external links ChrisN Excel Discussion (Misc queries) 1 April 2nd 06 06:05 PM
Problem with updating links in Excel 2003 Mike Gallof Excel Worksheet Functions 1 October 14th 05 05:17 PM
Problem with updating links in Excel 2003 Mike Gallof Excel Worksheet Functions 0 October 14th 05 04:39 PM


All times are GMT +1. The time now is 09:49 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"