R1C1 problems - Can't change

[Nargile]

Hei guys.

I'm currently helping a friend of mine setting up some spreadsheets,
and something weird has started to happen. We now have 7 different
spreadsheet, which all automatically changes to R1C1-format. We started
with one, and trying to make it a bit easier on the eye, we cut'n'pasted
some stuff around and changed the formulas accordingly(all formulas are
simple SUMs of 3 or 4 other cells)
Suddenly, whenever we copy or move a cell to another, the spreadsheet
automatically switches to R1C1-format and all formulas change. That was
only the one, and the other ones were working fine, until we copied a
cell(a number) from one spreadsheet to another, and then the other one
suddenly changed aswell.. So after moving stuff back and forth to have
the setup of sheets we(he) wanted etc we now have 7 sheets with the
same disease.. I know how to switch off R1C1, and we do, but as soon as
anything is changed/added/removed in the spreadsheet, it jumps back to
running R1C1..

Any ideas would be much appreciated!

 

[CLR]

Could be you have a Change-event macro that is re-setting the Reference Style
.....
Try starting the file with macros disabled......then change it back and see
if it automatically re-changes again.

Vaya con Dios,
Chuck, CABGx3

 

[Nargile]

Sorry, should have mentioned. Tried that actually, but didn't work..

 

[CLR]

Assuming the problem is only wiht this one file, then Major surgery may be in
order .......copying all data out of that workbook into a new one........one
sheet at a time, one module at a time, to see when/if the problem pops back
up..........

Vaya con Dios,
Chuck, CABGx3

 

[Nargile]

Hehe now this is where it gets interresting! Like it says, it "spreads
when we copy numbers from one workbook to another.. (sorry if i ge
confused with sheets, books etc). The point is, these are differen
xls-files. Copying to a brand new one doesn't help. Printing an
writing everything in by hand works..

Also: I tried uploading the file to google-spreadsheet and the
downloading it again after opening it online, and it actually work
fine after that(only our pretty colours and frames are gone..:

 

[CLR]

So apparently your problem in over now?

Every once in a while I have experienced the need to "rebuild" a workbook
from scratch. Sometimes by copying portions over to another workbook,
sometimes by actually having to hand re-enter the data, but in these really
confuzing situations, it usually works.

Vaya con Dios,
Chuck, CABGx3

 

[Dave Peterson]

More things to check...

If you open excel in safe mode:

Close excel
windows start button|Run
excel /safe

File|Open yourworkbookname.xls

Do you still have the same trouble?

In safe mode, excel won't allow macros to run. If the problem goes away, maybe
it's a macro that's causing the trouble--in an addin???

I'd start looking for this "helpful" addin.

Chip Pearson has some notes on how to diagnose startup errors:
http://www.cpearson.com/excel/StartupErrors.htm

And Jan Karel Pieterse has more notes:
http://www.jkp-ads.com/Articles/StartupProblems.asp

 

[Nargile]

Well the problem is over if we open it in google spreadsheet and then
download it locally again, but it's annoying, and i'd really like to
figure out how/why this happened.
Thanks for the tips! I'm away for the weekend, but will check it on
monday.

 

[Nargile]

Hmm.. Ok I tried everything i could find on bugs etc, and the tips and
links you gave me, but nothing. No matter how I open the file, it still
changes back to R1C1-style as soon as anything is changed in the file.

 

[Dave Peterson]

You opened in safe mode and still had the trouble.

Save a new workbook in A1 reference style.

Close excel.

reopen excel in safe mode.

Is that initial workbook shown in A1 or R1C1 reference style.

If it's A1, then try opening the test workbook that you just saved. Is excel
still in A1 reference style?

Excel picks this up from the first workbook you open in that session. Maybe
it's your personal.xls workbook--or an addin.

You sure you followed Chip/Jan Karel's instructions when you were checking
things out??

 

[Nargile]

Yup, sure I followed the tips. And opening another workbook with A1
works fine. Thing is, the document I'm struggling with also opens as
A!, until i take a copy of a cell, or do anything with the document at
all, it changes back. Don't even have to do anything so any formulas
are updated. Just CTRL+C an empty cell, and every formula in the
workbook is suddenly R1C1.

 

[CLR]

When you say that copying to another workbook doesnt help, do you mean to say
that copying even one sheet or even one formula to a new workbook will infect
the whole workbook?........Does this file act like this when run on another
computer?

Vaya con Dios,
Chuck, CABGx3

 

[Nargile]

Yes and yes.
On any computer it will do this to all workbooks where you copy from
this workbook to another workbook that works fine. It's like a virus...
only not.. =)

 

[CLR]

How about if you open a new workbook and "link" the data from the errant book
over to the new one, don't copy and paste it..........try one sheet at a
time.......does the problem still transfer to the new book?

Vaya con Dios,
Chuck, CABGx3

 

[Dave Peterson]

I can't think of an event built into excel that would fire when you hit ctrl-c.

I don't have another guess.

Good luck,

 

[BizMark]

I have had periods of experiencing this problem too - which later on has
then just stopped - then a few months later it will happen again.

Even when no workbooks I've been using were ever set to R1C1 format.

This is a quirk I've just learned to live with. No macros or settings
indicate that it should be happening, but sometimes - it just does.

Regards,
BizMark

 

[Nargile]

Hehe ok.

I've fixed it by uploading all workbooks to google spreadsheet and
re-saving them.

Thanks for the help everyone! I give up finding the reason!