this is my formula
='H:\EXCELDAT\MATRIX\CM2006\[DKBSD06.XLS]FLAT BSD #2 PT'!$D$288
i need to change CM2006 to COST MATRIX 2006 in a column containing 3,500
cells.

when selecting a 3,500 cell column to replace items in the formula, i am
allowed to replace one by one, but when select replace all, excel returns $0
and then locks up.

help?

Try using Text to Columns to evaluate external formulas. This is much
quicker as it does not relink to the source file.

1. Replace "=" with "'=" (precede = by apostrophe to change formula to
text).
2. Replace "CM2006" with "COST MATRIX 2006"
3. Select column and then DataText to ColumnsDelimitedTabFinish to
evaluate.


On 24 May, 15:02, Rob wrote:
this is my formula
='H:\EXCELDAT\MATRIX\CM2006\[DKBSD06.XLS]FLAT BSD #2 PT'!$D$288
i need to change CM2006 to COST MATRIX 2006 in a column containing 3,500
cells.

when selecting a 3,500 cell column to replace items in the formula, i am
allowed to replace one by one, but when select replace all, excel returns $0
and then locks up.

help?

Just a warning.

Using '= changes the .prefixcharacter setting and can cause trouble later.

I'd replace = with $$$$$=
and then edit|replace to change it back to = after the changes.

And I'd open that sending workbook, too. I bet it would calculate faster--and
maybe stop the lockup????

Lori wrote:

Try using Text to Columns to evaluate external formulas. This is much
quicker as it does not relink to the source file.

1. Replace "=" with "'=" (precede = by apostrophe to change formula to
text).
2. Replace "CM2006" with "COST MATRIX 2006"
3. Select column and then DataText to ColumnsDelimitedTabFinish to
evaluate.

On 24 May, 15:02, Rob wrote:
this is my formula
='H:\EXCELDAT\MATRIX\CM2006\[DKBSD06.XLS]FLAT BSD #2 PT'!$D$288
i need to change CM2006 to COST MATRIX 2006 in a column containing 3,500
cells.

when selecting a 3,500 cell column to replace items in the formula, i am
allowed to replace one by one, but when select replace all, excel returns $0
and then locks up.

help?

--

Dave Peterson

I suspect there are multiple sending workbooks as it is the folder
name that is being replaced, and if so opening all workbooks within
that folder could be troublesome.

Thanks for pointing out the prefix character steeing, I hadn't noticed
that this was a persistent setting before. I can't see it being a
major issue here though and you could get round it by outputting to
another column column in the third step of text to columns and
deleting the existing one. I also meant to add that you may need to do
an edit links update.



On 24 May, 16:12, Dave Peterson wrote:
Just a warning.

Using '= changes the .prefixcharacter setting and can cause trouble later.

I'd replace = with $$$$$=
and then edit|replace to change it back to = after the changes.

And I'd open that sending workbook, too. I bet it would calculate faster--and
maybe stop the lockup????





Lori wrote:

Try using Text to Columns to evaluate external formulas. This is much
quicker as it does not relink to the source file.

1. Replace "=" with "'=" (precede = by apostrophe to change formula to
text).
2. Replace "CM2006" with "COST MATRIX 2006"
3. Select column and then DataText to ColumnsDelimitedTabFinish to
evaluate.

On 24 May, 15:02, Rob wrote:
this is my formula
='H:\EXCELDAT\MATRIX\CM2006\[DKBSD06.XLS]FLAT BSD #2 PT'!$D$288
i need to change CM2006 to COST MATRIX 2006 in a column containing 3,500
cells.

when selecting a 3,500 cell column to replace items in the formula, i am
allowed to replace one by one, but when select replace all, excel returns $0
and then locks up.

help?

--

Dave Peterson- Hide quoted text -

- Show quoted text -

There are ways to work around the .prefixcharacter problem. But I find it
easier to just not cause it.

And maybe there are multiple sending workbooks--if that number is small, I'd
still try to open them all at once. (Small purposely left vague <vbg.)

Lori wrote:

I suspect there are multiple sending workbooks as it is the folder
name that is being replaced, and if so opening all workbooks within
that folder could be troublesome.

Thanks for pointing out the prefix character steeing, I hadn't noticed
that this was a persistent setting before. I can't see it being a
major issue here though and you could get round it by outputting to
another column column in the third step of text to columns and
deleting the existing one. I also meant to add that you may need to do
an edit links update.

On 24 May, 16:12, Dave Peterson wrote:
Just a warning.

Using '= changes the .prefixcharacter setting and can cause trouble later.

I'd replace = with $$$$$=
and then edit|replace to change it back to = after the changes.

And I'd open that sending workbook, too. I bet it would calculate faster--and
maybe stop the lockup????





Lori wrote:

Try using Text to Columns to evaluate external formulas. This is much
quicker as it does not relink to the source file.

1. Replace "=" with "'=" (precede = by apostrophe to change formula to
text).
2. Replace "CM2006" with "COST MATRIX 2006"
3. Select column and then DataText to ColumnsDelimitedTabFinish to
evaluate.

On 24 May, 15:02, Rob wrote:
this is my formula
='H:\EXCELDAT\MATRIX\CM2006\[DKBSD06.XLS]FLAT BSD #2 PT'!$D$288
i need to change CM2006 to COST MATRIX 2006 in a column containing 3,500
cells.

when selecting a 3,500 cell column to replace items in the formula, i am
allowed to replace one by one, but when select replace all, excel returns $0
and then locks up.

help?

--

Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson

True, it seems there were only a handful of links at most and simple
is almost always best as here <g.

FWIW using ["=] instead of ['=] would avoid the prefix character and
still evaluate since " is the default text qualifier.


On May 24, 7:46 pm, Dave Peterson wrote:
There are ways to work around the .prefixcharacter problem. But I find it
easier to just not cause it.

And maybe there are multiple sending workbooks--if that number is small, I'd
still try to open them all at once. (Small purposely left vague <vbg.)





Lori wrote:

I suspect there are multiple sending workbooks as it is the folder
name that is being replaced, and if so opening all workbooks within
that folder could be troublesome.

Thanks for pointing out the prefix character steeing, I hadn't noticed
that this was a persistent setting before. I can't see it being a
major issue here though and you could get round it by outputting to
another column column in the third step of text to columns and
deleting the existing one. I also meant to add that you may need to do
an edit links update.

On 24 May, 16:12, Dave Peterson wrote:
Just a warning.

Using '= changes the .prefixcharacter setting and can cause trouble later.

I'd replace = with $$$$$=
and then edit|replace to change it back to = after the changes.

And I'd open that sending workbook, too. I bet it would calculate faster--and
maybe stop the lockup????

Lori wrote:

Try using Text to Columns to evaluate external formulas. This is much
quicker as it does not relink to the source file.

1. Replace "=" with "'=" (precede = by apostrophe to change formula to
text).
2. Replace "CM2006" with "COST MATRIX 2006"
3. Select column and then DataText to ColumnsDelimitedTabFinish to
evaluate.

On 24 May, 15:02, Rob wrote:
this is my formula
='H:\EXCELDAT\MATRIX\CM2006\[DKBSD06.XLS]FLAT BSD #2 PT'!$D$288
i need to change CM2006 to COST MATRIX 2006 in a column containing 3,500
cells.

when selecting a 3,500 cell column to replace items in the formula, i am
allowed to replace one by one, but when select replace all, excel returns $0
and then locks up.

help?

--

Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson- Hide quoted text -

- Show quoted text -

And from the OP's response, it sounds like there was only one file--or a minor
number of files if more than one.

Lori wrote:

I suspect there are multiple sending workbooks as it is the folder
name that is being replaced, and if so opening all workbooks within
that folder could be troublesome.

Thanks for pointing out the prefix character steeing, I hadn't noticed
that this was a persistent setting before. I can't see it being a
major issue here though and you could get round it by outputting to
another column column in the third step of text to columns and
deleting the existing one. I also meant to add that you may need to do
an edit links update.

On 24 May, 16:12, Dave Peterson wrote:
Just a warning.

Using '= changes the .prefixcharacter setting and can cause trouble later.

I'd replace = with $$$$$=
and then edit|replace to change it back to = after the changes.

And I'd open that sending workbook, too. I bet it would calculate faster--and
maybe stop the lockup????





Lori wrote:

Try using Text to Columns to evaluate external formulas. This is much
quicker as it does not relink to the source file.

1. Replace "=" with "'=" (precede = by apostrophe to change formula to
text).
2. Replace "CM2006" with "COST MATRIX 2006"
3. Select column and then DataText to ColumnsDelimitedTabFinish to
evaluate.

On 24 May, 15:02, Rob wrote:
this is my formula
='H:\EXCELDAT\MATRIX\CM2006\[DKBSD06.XLS]FLAT BSD #2 PT'!$D$288
i need to change CM2006 to COST MATRIX 2006 in a column containing 3,500
cells.

when selecting a 3,500 cell column to replace items in the formula, i am
allowed to replace one by one, but when select replace all, excel returns $0
and then locks up.

help?

--

Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson

Try turning automatic calculation off
Also try opening the linked file before executing the search & replace

"Rob" wrote:

this is my formula
='H:\EXCELDAT\MATRIX\CM2006\[DKBSD06.XLS]FLAT BSD #2 PT'!$D$288
i need to change CM2006 to COST MATRIX 2006 in a column containing 3,500
cells.

when selecting a 3,500 cell column to replace items in the formula, i am
allowed to replace one by one, but when select replace all, excel returns $0
and then locks up.

help?

thanks...worked like a charm.

"Duke Carey" wrote:

Try turning automatic calculation off
Also try opening the linked file before executing the search & replace

"Rob" wrote:

this is my formula
='H:\EXCELDAT\MATRIX\CM2006\[DKBSD06.XLS]FLAT BSD #2 PT'!$D$288
i need to change CM2006 to COST MATRIX 2006 in a column containing 3,500
cells.

when selecting a 3,500 cell column to replace items in the formula, i am
allowed to replace one by one, but when select replace all, excel returns $0
and then locks up.

help?

Hi Rob,

Try doing a search and replace = sign with the = sign, then recalc.

Let me know how that worked.

Thanks,
Peggy

"Rob" wrote:

this is my formula
='H:\EXCELDAT\MATRIX\CM2006\[DKBSD06.XLS]FLAT BSD #2 PT'!$D$288
i need to change CM2006 to COST MATRIX 2006 in a column containing 3,500
cells.

when selecting a 3,500 cell column to replace items in the formula, i am
allowed to replace one by one, but when select replace all, excel returns $0
and then locks up.

help?

Sorry to revive such an old tread, but I am attempting to do the same thing.

Here is what I have tried to so far:

I converted all the formulas to text by using the find = and replace with
$$$=. This was very fast.

I changed my workbook reference from jan-june to july-dec using the find and
replace. Again this was very fast.

Here is my hangup...

When I attempt to change the $$$= back to = using find and replace it slows
excel to a halt, and I end up having to use task manager to get out of it.

This even happens when I have manual calculations turned off.

Is there a step I am missing to complete this process, or am I going about
this all wrong?

Thanks,

Diane

"pshepard" wrote:

Hi Rob,

Try doing a search and replace = sign with the = sign, then recalc.

Let me know how that worked.

Thanks,
Peggy

"Rob" wrote:

this is my formula
='H:\EXCELDAT\MATRIX\CM2006\[DKBSD06.XLS]FLAT BSD #2 PT'!$D$288
i need to change CM2006 to COST MATRIX 2006 in a column containing 3,500
cells.

when selecting a 3,500 cell column to replace items in the formula, i am
allowed to replace one by one, but when select replace all, excel returns $0
and then locks up.

help?

Did you try the Text to Columns method?

Replace = with "= then select the column and choose
Data Text To Columns with Delimited and click Finish.

You can then update links by in the Edit Links dialog.

On Jan 21, 8:01*pm, diaare wrote:
Sorry to revive such an old tread, but I am attempting to do the same thing.

Here is what I have tried to so far:

I converted all the formulas to text by using the find = and replace with
$$$=. *This was very fast.

I changed my workbook reference from jan-june to july-dec using the find and
replace. *Again this was very fast.

Here is my hangup...

When I attempt to change the $$$= back to = using find and replace it slows
excel to a halt, and I end up having to use task manager to get out of it.

This even happens when I have manual calculations turned off. *

Is there a step I am missing to complete this process, or am I going about
this all wrong?

Thanks,

Diane



"pshepard" wrote:
Hi Rob,

Try doing a search and replace = sign with the = sign, then recalc.

Let me know how that worked.

Thanks,
Peggy

"Rob" wrote:

this is my formula *
='H:\EXCELDAT\MATRIX\CM2006\[DKBSD06.XLS]FLAT BSD #2 PT'!$D$288
i need to change CM2006 to COST MATRIX 2006 in a column containing 3,500
cells.

when selecting a 3,500 cell column to replace items in the formula, i am
allowed to replace one by one, but when select replace all, excel returns $0
and then locks up.

help?- Hide quoted text -

- Show quoted text -

If you open the "sending" file, does it work faster?

diaare wrote:

Sorry to revive such an old tread, but I am attempting to do the same thing.

Here is what I have tried to so far:

I converted all the formulas to text by using the find = and replace with
$$$=. This was very fast.

I changed my workbook reference from jan-june to july-dec using the find and
replace. Again this was very fast.

Here is my hangup...

When I attempt to change the $$$= back to = using find and replace it slows
excel to a halt, and I end up having to use task manager to get out of it.

This even happens when I have manual calculations turned off.

Is there a step I am missing to complete this process, or am I going about
this all wrong?

Thanks,

Diane

"pshepard" wrote:

Hi Rob,

Try doing a search and replace = sign with the = sign, then recalc.

Let me know how that worked.

Thanks,
Peggy

"Rob" wrote:

this is my formula
='H:\EXCELDAT\MATRIX\CM2006\[DKBSD06.XLS]FLAT BSD #2 PT'!$D$288
i need to change CM2006 to COST MATRIX 2006 in a column containing 3,500
cells.

when selecting a 3,500 cell column to replace items in the formula, i am
allowed to replace one by one, but when select replace all, excel returns $0
and then locks up.

help?

--

Dave Peterson

If there's just one filename, why not just change the link by double-
clicking the old file name in the Edit Links dialog, this seems
simpler than the Replace method in the original post as well.

On Jan 22, 4:43*pm, Dave Peterson wrote:
If you open the "sending" file, does it work faster?





diaare wrote:

Sorry to revive such an old tread, but I am attempting to do the same thing.

Here is what I have tried to so far:

I converted all the formulas to text by using the find = and replace with
$$$=. *This was very fast.

I changed my workbook reference from jan-june to july-dec using the find and
replace. *Again this was very fast.

Here is my hangup...

When I attempt to change the $$$= back to = using find and replace it slows
excel to a halt, and I end up having to use task manager to get out of it.

Thank you both for your help.

Unfortunately I was not able to use the edit links function because I was
only changing the tab it referenced, not the workbook. (unless you know
something I don't know)

But, after a little more trial and error with a comination of your
suggestions I was finally able to get it to work...it still took a few
minutes, but it completed without crashing excel.

BTW - the most crutical thing I forgot was to open the linked
workbook...duh! Sometimes the most obvious thing is all it takes....

Thanks Again,

Diane

"Lori" wrote:

If there's just one filename, why not just change the link by double-
clicking the old file name in the Edit Links dialog, this seems
simpler than the Replace method in the original post as well.

On Jan 22, 4:43 pm, Dave Peterson wrote:
If you open the "sending" file, does it work faster?





diaare wrote:

Sorry to revive such an old tread, but I am attempting to do the same thing.

Here is what I have tried to so far:

I converted all the formulas to text by using the find = and replace with
$$$=. This was very fast.

I changed my workbook reference from jan-june to july-dec using the find and
replace. Again this was very fast.

Here is my hangup...

When I attempt to change the $$$= back to = using find and replace it slows
excel to a halt, and I end up having to use task manager to get out of it.

This even happens when I have manual calculations turned off.

Is there a step I am missing to complete this process, or am I going about
this all wrong?

Thanks,

Diane

"pshepard" wrote:

Hi Rob,

Try doing a search and replace = sign with the = sign, then recalc..

Let me know how that worked.

Thanks,
Peggy

"Rob" wrote:

this is my formula
='H:\EXCELDAT\MATRIX\CM2006\[DKBSD06.XLS]FLAT BSD #2 PT'!$D$288
i need to change CM2006 to COST MATRIX 2006 in a column containing 3,500
cells.

when selecting a 3,500 cell column to replace items in the formula, i am
allowed to replace one by one, but when select replace all, excel returns $0
and then locks up.

help?

--

Dave Peterson- Hide quoted text -

- Show quoted text -