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?

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

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
>

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