+ Reply to Thread
Results 1 to 5 of 5

Reference nth row from another sheet

  1. #1
    Registered User
    Join Date
    10-28-2011
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    78

    Reference nth row from another sheet

    In cell T34 of my first sheet, i want to reference cell AG84 from a second sheet, and from there copy the formula down on the first sheet so the results would display reesults from AG88, AG92 ...every 4th cell from the second sheet.

    I have tried the following index formula, and have had no luck;

    =INDEX('Savings Sheet'!$AG:$AG,4*(ROWS('Savings Sheet'!AG84)-1)+1)

    I searched and that's where I came up with the index formula. I'm sure I just have it set up wrong though.
    Last edited by dta1984; 11-02-2011 at 04:42 PM.

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Reference nth row from another sheet

    =INDEX('savings sheet'!$AG$84:$AG$2000,ROWS($A$1:A1)*4-3)
    or
    =INDEX('savings sheet'!$AG:$AG,83+ROWS($A$1:A1)*4-3)
    Last edited by martindwilson; 11-02-2011 at 04:08 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    10-28-2011
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Reference nth row from another sheet

    Quote Originally Posted by martindwilson View Post
    =INDEX('savings sheet'!$AG$84:$AG$2000,ROWS($A$1:A1)*4-3)
    or
    =INDEX('savings sheet'!$AG:$AG,83+ROWS($A$1:A1)*4-3)
    Those return my first value from (AG84), but when i copy it down, I just get 0s. From here, I need AG88, AG92 etc. Can you explain the "ROWS($A$1:A1) part of the formula?

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Reference nth row from another sheet

    works for me see sheet 1
    ROWS($A$1:A1)=1
    ROWS($A$1:A2)=2
    ROWS($A$1:A3)=3
    and so on as it's dragged down
    Attached Files Attached Files
    Last edited by martindwilson; 11-02-2011 at 04:32 PM.

  5. #5
    Registered User
    Join Date
    10-28-2011
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Reference nth row from another sheet

    Quote Originally Posted by martindwilson View Post
    works for me see sheet 1
    ROWS($A$1:A1)=1
    ROWS($A$1:A2)=2
    ROWS($A$1:A3)=3
    and so on as it's dragged down
    Yours definitely works perfectly. Even copied exactly I get nothing. Can my rows can be any or do they have to be A.... The sheet is filled with data

    Edit; I got it, Thanks!!!
    Last edited by dta1984; 11-02-2011 at 04:42 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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