Shared Excel file (Excel 97, SR2)

Topic

New Reply

I have a spreadsheet that is shared among several users. When someone is in the spreadsheet, I get the read-only message to let me know that the file is being used. Today, I went to enter my data and noticed that the formulas that reference another sheet had been changed by one column. The spreadsheet is large and there are tons of formulas…so I know that no one made changes to all the formulas. Is there a bug in excel? We are using Excel 97, Sr2.

This has happened several times in the past month and I am really puzzled. There are no new users and no changes in our network.

Any suggestions????
Deborah

Reply | Quote

Replies

Deborah

Let me get it straight, so a formula say in A1 such as =Sum(A2:A3) now suddenly says =Sum(B2:B3) or is the formula actually now in B1?

This sounds like a nightmare. You must be sure that no one altered these formulas?

I suggest you protect the worksheet(s) minus the cells that change and only tell the password to two other people. This should minimize any user changes.

Also is there any VBA code that adds columns or what have you?

This is the first time I hear something like this happening, and you may want to rebuild the workbook. This maybe a sign of some serious corruption specially that you have others working on the same workbook. Possibly make a copy of it in a secure place and then start rebuilding the workbook.
What is the size in Mb of this workbook?
What size would you anticipate it to be?
How much formatting is in this workbook? The more you have the more the workbook size.

Good luck and let us know what you find out.

Wassim

Reply | Quote

You have the idea. I found a potential problem but don’t know how to get out of it. There is a link!!!! The file was an given to me to take over and I never noticed that there was a link. How do I remove the link? Also, if I go to the VBA editor it shows a VBA project. How do I remove this?

Thanks

Reply | Quote

Deborah

To remove a link you need to follow this procedure. Now this is ugly, you can fetch a copy of Bill Manville’s “Link Hunter” Not the actual name of the Add-In, but its out there someplace, and it is great.

1) From the Edit Menu choose Links…
2) The Links Dialog will show, and you will need to look for something under the Source File.
3) Grab a unique portion of what you see under Source File.
4) Search EVERYWHERE for that string. Each time you find it decide what to do with it.

Remember links can be ( Oh My God) Everywhere, in charts in names, in objects and in formulas…

So here is your task. I still don’t think this is the problem, but at this point, heck anything can be.

For the VBA stuff, if you have the Macro protection on, Tools|Options|General Tab| Macro Virus protection should be checked, then each time you open the workbook it should give you a warning, if you don’t see that warning chances are you don’t have VBA code. The VBA project you see, I suspect is the workbook itself. You should see something like VBAProject (Name of the workbook). This is fine, and you should not remove it.

If you can wait, I could send a message to Bill Manville and ask him to send you a copy of his Add-In, so send me your e-mail and I’ll pass it on. No promises though.

Keep digging, there must be something out there. But also answer my questions in the earlier reply. I am curiouse.

Wassim

Reply | Quote

Try this Star Post on locating links for some useful references and discussion. It mentions where to go to download the Bill Mannville and the MS tool for links.

Cheers

Reply | Quote

Has anyone inserted a column in the sheet? If you have a formula in A1, say =D1+E1, and you insert a coulmn beteen A and B, the fromula will be adjusted to be =E1+F1. Could that be what is happening to you?

Reply | Quote