1. Not sure what happened, but I just opened a spreadsheet (Excel 2007) and although my rows are still numbered... my COLUMNS are now NUMBERS as well. Anyone know what happened? When I composed the formulas originally everything was conventional row numbers and column letters... now a cell that used to be A1 is now R1C1. A formula that used to read =SUM(C3:C8) now reads =SUM(R[-6]C:R[-1]C). What the????

Thanks,
Steve

2. Normal Row Column style is turned off accidentally.

Go to the BIG Button top left, and then choose Excel Options button at Bottom right

Select the Formulas Tab

Then Make sure that the R1C1 tick box is clear as shown below

[attachment=88273:R1C1.jpg]

3. Originally Posted by steve pratt
A formula that used to read =SUM(C3:C8) now reads =SUM(R[-6]C:R[-1]C). What the????
Steve,

Just in case you're interested the formula =SUM(R[-6]C:R[-1]C) is = to SUM(C3:C8). The way it is interpreted is from the cell where the formula resides you count up (the -) 6 cells and stay in the current column (C w/o number) all this = C3 (sub-total being in c9-6=c3). Use te same logic for the second half of the reference. Negative numbers go up (smaller row numbers) and left(lower letters) while positive numbers go down (higher row numbers) and right(higher column letters).

This type of referencing is especially useful in writing VBA code as those numbers can be replaced by variables allowing you to construct formulas on the fly...it's a lot easier to calculate w/numbers than letters.

I hope this helps and isn't too confusing. The reason for the capability to switch how you see the references on the sheet is so that you can check your code w/o converting letters like LV to numbers in your head on on your fingers!

RG

4. Originally Posted by Andrew W
Normal Row Column style is turned off accidentally.

Go to the BIG Button top left, and then choose Excel Options button at Bottom right

Select the Formulas Tab

Then Make sure that the R1C1 tick box is clear as shown below

[attachment=88273:R1C1.jpg]
That was IT! Thanks! I wonder.... I did record a couple of macros within this worksheet... I wonder if this setting became active because of that? I have never been to that TAB before, so don't see how it could have "accidentally been checked????

Thanks to all.
Steve

5. This would change the reference to R1C1 (checked box)
Application.ReferenceStyle = xlR1C1

this would change the reference to A1 (unchecked box)
Application.ReferenceStyle = xlA1

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•