Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Nov 2009
    Location
    The Great NW
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Attached Images Attached Images

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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]
    Attached Images Attached Images
    Andrew

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by steve pratt View Post
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    New Lounger
    Join Date
    Nov 2009
    Location
    The Great NW
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Andrew W View Post
    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. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
  •