+ Reply to Thread
Results 1 to 9 of 9

Drag a formula that references a cell from another Sheet (Skipping a cell incrementing 1)

  1. #1
    Registered User
    Join Date
    09-04-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    13

    Drag a formula that references a cell from another Sheet (Skipping a cell incrementing 1)

    Hi, I have a formula in Sheet2 A1 ='Sheet1!A1
    On Sheet2 A2 I have =A1 in the cell

    When I drag this down in Sheet2 I need every other cell to increase by one so that I get this output

    A1 = SDI[1] "COMES FROM SHEET 1 CELL A1"
    A2 = SDI[1] = A1
    A3 = SDI[2] "COMES FROM SHEET 1 CELL A2"
    A4 = SDI[2] = A3

    AND SO ON

    This is the output I am currently getting

    A1 = SDI[1] "COMES FROM SHEET 1 CELL A1"
    A2 = SDI[1] = A1
    A3 = SDI[3] "COMES FROM SHEET 1 CELL A3"
    A4 = SDI[3] = A3




    Is this possible, i am racking my brain.

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Drag a formula that references a cell from another Sheet (Skipping a cell incrementing

    hi DixieDriver, welcome to the forum. try in A1:
    =INDEX(Sheet1!A:A,(ROWS(A$1:A1)-1)+MOD(ROWS(A$1:A1),2))

    copy down.

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    09-04-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Drag a formula that references a cell from another Sheet (Skipping a cell incrementing

    Thanks for responding. I played with the formula, and i still cant get it to work. I'll try and explain this a little better. I have a cell on Sheet2 (F3), this cell references a cell on Sheet1, (B2) with the Text "SDI[0]". On sheet two in cell (F4) i have the formula =(F3). I then highlight Cells F3 and F4 and drag down.

    So on Sheet1 in collum B i have:
    B2 - SDI[0]
    B3 - SDI[1]
    B4 - SDI[3]
    ect

    Sheet2
    F3 Formula is =Sheet1 B2 wich is SDI[0]
    F4 formula is =F3 wich is SDI[0]

    So when i Drag cell F3 and F4 down cells F5 and F6 should be:
    F5 = SDI[1]
    F6 = SDI[1]

    ect

  4. #4
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: Drag a formula that references a cell from another Sheet (Skipping a cell incrementing

    Maybe....

    Sheet2 F3 formula
    =INDEX(Sheet1!B:B,INT((ROWS($F$3:$F3)+1)/2)+1)
    copy down
    Marcelo Branco

  5. #5
    Registered User
    Join Date
    09-04-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Drag a formula that references a cell from another Sheet (Skipping a cell incrementing

    Thanks a million, you just saved me days of work.

  6. #6
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: Drag a formula that references a cell from another Sheet (Skipping a cell incrementing

    Quote Originally Posted by DixieDiver View Post
    Thanks a million, you just saved me days of work.
    You are welcome and thanks for the feedback

  7. #7
    Registered User
    Join Date
    09-04-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Drag a formula that references a cell from another Sheet (Skipping a cell incrementing

    I would Like to add one more tweak to this formula, how would I edit the following formula to not return a 0 if the cell I am referenceing is blank

    =INDEX('HAS-LAW SDI'!C:C,INT((ROWS($F$3:$F4)+1)/2)+1)

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,933

    Re: Drag a formula that references a cell from another Sheet (Skipping a cell incrementing

    maybe...
    =if(INDEX('HAS-LAW SDI'!C:C,INT((ROWS($F$3:$F4)+1)/2)+1)=0,"",INDEX('HAS-LAW SDI'!C:C,INT((ROWS($F$3:$F4)+1)/2)+1))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  9. #9
    Registered User
    Join Date
    09-04-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Drag a formula that references a cell from another Sheet (Skipping a cell incrementing

    Thanks, that did the trick!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Drag all cell references when editing formula
    By forfriends in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-01-2019, 12:59 PM
  2. How to drag a formula while skipping every other cell reference
    By sbham in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-13-2013, 06:17 AM
  3. Drag formula down and skip 2 cell references instead of 1 cell reference
    By cat2005 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-09-2013, 11:45 AM
  4. Replies: 5
    Last Post: 03-07-2011, 12:34 PM
  5. Incrementing cell references
    By smiso24 in forum Excel General
    Replies: 3
    Last Post: 05-15-2010, 11:37 AM

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