Cell reference problem (EXcel 2003 SP3)

Topic

New Reply

Good morning

I have a workbook that has been OK for some 3 years but today for some reason it is not seeing the cell references (and there are many 1000’s) in any of the worksheets. For example I have =Setup!$A$301 but it is not showing the result as it should. If I delete it and retype in =Setup!A301 or even retype in =Setup!$A$301 it works OK, so I tried a replace for all of the worksheets to replace $ with nothing and it says it has made 27,000+ changes but then hangs until Excel says it has stopped responding.

Any pointers would be greatly appreciated otherwise I will be several days (weeks) retying in every single reference.

Reply | Quote

Replies

so I tried a replace for all of the worksheets to replace $ with nothing and it says it has made 27,000+ changes but then hangs until Excel says it has stopped responding.

I would prefer to let the excel perform the task its own way. Not responding message appears on interruption only.

BTW, are you trying to replace the contents for all the ws’s in one go?

Reply | Quote

I would prefer to let the excel perform the task its own way. Not responding message appears on interruption only.

BTW, are you trying to replace the contents for all the ws’s in one go?

Hi Prasad

Thanks for the response. I have tried per weeksheet and per workbook to do the replace, whichever I try it says it is has made the replacement but the replace box remains on the screen, I have tried several times and even if I go and leave it for 2 hours+ the replace box is still there and the only way to stop it is to use the’End Task’ function (Close or the X does not stop it).

I have tried the replace per section in worksheets, by worksheet and by workbook with the same results

Reply | Quote

See if this could help you.

Reply | Quote

See if this could help you.

Hi Prasad

This Macro seems to be a ‘find’ function and not a replace one? I have run it but it does not seem to do anything

Thanks anyway

Reply | Quote

…..I have a workbook that has been OK for some 3 years but ….. it is not seeing the cell references (and there are many 1000’s) in any of the worksheets. …

It’s beginning to seem to me that perhaps the workbook is starting to become corrupted.

Was the workbook originally built on Excel 2003?

Is it a shared workbook?

Has it, over the years, had various entries from other sources pasted into it (together with the source formatting) from elsewhere? (Often, a simple way to check whether other material has been imported is to use Ctrl + 1 -> Number -> Custom & then scroll down the dialogBox to see how many extra formats have accumulated.)

If corruption has started, you may need to rebuild it. Rebuilding doesn’t mean rekeying in all the entries. It *does* mean copying over all the formulas, formatting & VBA code to a pristine workbook – as well as removing any Links to the sourcing workbook.

Hope This Helps

Reply | Quote

It’s beginning to seem to me that perhaps the workbook is starting to become corrupted.

Was the workbook originally built on Excel 2003?

Is it a shared workbook?

Has it, over the years, had various entries from other sources pasted into it (together with the source formatting) from elsewhere? (Often, a simple way to check whether other material has been imported is to use Ctrl + 1 -> Number -> Custom & then scroll down the dialogBox to see how many extra formats have accumulated.)

If corruption has started, you may need to rebuild it. Rebuilding doesn’t mean rekeying in all the entries. It *does* mean copying over all the formulas, formatting & VBA code to a pristine workbook – as well as removing any Links to the sourcing workbook.

Hope This Helps

Hi Malcolm

Thank you for the advice. This workbook was created by me (with help from the Woody’s loungers) has no external links or hyperlinks and does not retrieve data from any other sources and receives data input via a combo box from only myself and 2 other users.

I had already though of this and run several of the tools from the ASAP to remove extra spaces, external links (which there should not be any of) etc. but that does not help.

In rebuilding, as per your suggestion, is it sufficient to copy and paste each sheet?

Reply | Quote

….This workbook was created by me (with help from the Woody’s loungers) has no external links or hyperlinks and does not retrieve data from any other sources and receives data input via a combo box from only myself and 2 other users…….

In rebuilding, as per your suggestion, is it sufficient to copy and paste each sheet?

Given what you say, the deterioration somewhat surprises me, as it did you.

If your fellow users are experiencing the same problem, then there should be no harm in trying a rebuild. If they are not, then it could well be something on your own machine.

Copy|pasting the contents should be fine. As you probably know, any links (back to the old workbook) that arise can be removed by Edit -> Links and then linking to the new workbook.

Reply | Quote