+ Reply to Thread
Results 1 to 7 of 7

[Excel 2007-2010] Range selection using .formulaR1C1

  1. #1
    Registered User
    Join Date
    09-25-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    6

    [Excel 2007-2010] Range selection using .formulaR1C1

    I am trying to create a subtotal for a cells J20>last used J (Lets say in this case it is J40) in Last used J +2 (J42)

    I tried many options but it does not seem to work very well.

    The closest I've gotten is below:

    Please Login or Register  to view this content.
    Also tried:
    Please Login or Register  to view this content.
    but that didn't work as well

    I think it may be with the fact that I am using R1C1 notation at the same time as A1 notation. How do can i make R1C1 notation variable?

    Any help/tips would be greatly appreciated.
    Last edited by vlady; 01-21-2013 at 07:40 PM. Reason: code tags

  2. #2
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    3,444

    Re: [Excel 2007-2010] Range selection using .formulaR1C1

    I have not looked at anything yet but Column J is 10, not 9.
    I'll have a look later on when time permits.
    If you enter the formula in A1 notation and then change your settings to RC notation, what does your formula show?

    Regards

    John

    After looking at it again, forget what I mentioned about the Column number.
    I should read it properly before answering.
    Sorry about that
    John
    Last edited by jolivanes; 01-21-2013 at 08:38 PM.

  3. #3
    Registered User
    Join Date
    09-25-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: [Excel 2007-2010] Range selection using .formulaR1C1

    Hi John,

    Thank you for looking at this.

    The 9 is so subtotal is using sum.

    As for the A1 notation vs RC notation:
    =SUBTOTAL(9,R[-22]C:R[-2]C)

    Thank you,

    Dodo

  4. #4
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    3,444

    Re: [Excel 2007-2010] Range selection using .formulaR1C1

    If it always start from J20, maybe something like this?


    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    3,444

    Re: [Excel 2007-2010] Range selection using .formulaR1C1

    If both the first and last cell are variable then you could try something like this.
    It does assume that you have nothing in Column J above the first value.
    If you do, change the J1 reference to J3 or J4


    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    09-25-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: [Excel 2007-2010] Range selection using .formulaR1C1

    Hi John,

    This ended up working perfectly. Thank you very much for your help.

    Cheers,
    Dodo

  7. #7
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    3,444

    Re: [Excel 2007-2010] Range selection using .formulaR1C1

    Dodo.
    Glad it worked for you

    Regards
    John

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1