+ Reply to Thread
Results 1 to 7 of 7

Excel Offset: Need Formula that Adjusts for Differences in Row Increments

  1. 06-25-2016, 04:28 PM #1
    rjj920
    rjj920 is offline
    Forum Contributor
    Join Date
    12-09-2015
    Location
    Virginia, USA
    MS-Off Ver
    2007
    Posts
    121

    Excel Offset: Need Formula that Adjusts for Differences in Row Increments

    Hello,

    I have Column A of my sheet that returns a value every other row when the cell that it is referencing has a value every row. These differences in increments are giving me fits. I'm close with a few formulas I've found online but none are working for me quite right.

    I need the following:

    A5=Sheet2!A2
    A6=Blank
    A7=Sheet2!A3
    A8=Blank
    A9=Sheet2!A4
    A10=Blank
    A11=Sheet2!A5

    etc. etc. etc.

    Any help would be much appreciated!

    Thanks

    RJ
    Reply With QuoteRegister To Reply
  2. 06-25-2016, 06:47 PM #2
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Excel Offset: Need Formula that Adjusts for Differences in Row Increments

    Try this...

    This formula is specific to the cells it's entered in. So, I'm going by what you said:

    A5=Sheet2!A2
    A6=Blank
    A7=Sheet2!A3
    A8=Blank
    A9=Sheet2!A4
    A10=Blank
    A11=Sheet2!A5
    Entered in A5 and copied down:

    =IF(MOD(ROW(),2),INDEX(Sheet2!A$2:A$100,CEILING(ROWS(A$5:A5)/2,1)),"")

    Adjust for the correct end of range where I've used down to A100.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  3. 06-25-2016, 07:00 PM #3
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,598

    Re: Excel Offset: Need Formula that Adjusts for Differences in Row Increments 

    Please Login or Register  to view this content.
    Ben Van Johnson
    Reply With QuoteRegister To Reply
  4. 06-27-2016, 05:01 PM #4
    rjj920
    rjj920 is offline
    Forum Contributor
    Join Date
    12-09-2015
    Location
    Virginia, USA
    MS-Off Ver
    2007
    Posts
    121

    Re: Excel Offset: Need Formula that Adjusts for Differences in Row Increments

    This worked perfectly! Thank you!
    Reply With QuoteRegister To Reply
  5. 06-27-2016, 05:19 PM #5
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Excel Offset: Need Formula that Adjusts for Differences in Row Increments

    You're welcome. Thanks for the feedback!
    Reply With QuoteRegister To Reply
  6. 06-12-2017, 06:16 PM #6
    rjj920
    rjj920 is offline
    Forum Contributor
    Join Date
    12-09-2015
    Location
    Virginia, USA
    MS-Off Ver
    2007
    Posts
    121

    Re: Excel Offset: Need Formula that Adjusts for Differences in Row Increments

    How would you suggest doing this for every third row?

    I'm looking for the following:

    A7= Sheet6!A2
    A8= Blank
    A9 = Blank
    A10 = Sheet6!A3
    A11= Blank
    A12= Blank
    A13 = Sheet6!A4

    Thanks in advance...
    Reply With QuoteRegister To Reply
  7. 06-12-2017, 08:45 PM #7
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,598

    Re: Excel Offset: Need Formula that Adjusts for Differences in Row Increments

    =IF(MOD(ROW(),3)<>0,"",INDEX(Sheet2!A:A,INT(ROW()/3)))
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Problem with excel formula increments
    By sthar in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-02-2016, 06:12 AM
  2. Formula to compare 4columns with differences & to produce summary of differences on she2
    By JEAN1972 in forum Excel General
    Replies: 0
    Last Post: 04-13-2015, 01:01 PM
  3. Excel 2010 Macro to increase a cell's formula by increments of one
    By Quagmire in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 11-20-2012, 05:11 PM
  4. Formula for exact penny increments, eliminate Excel's precision variances
    By StuNVA in forum Excel General
    Replies: 2
    Last Post: 01-17-2011, 11:55 PM
  5. Formula that keeps track and adjusts multiple values
    By rwhite713 in forum Excel General
    Replies: 4
    Last Post: 08-12-2010, 08:48 PM
  6. Copying exact formula instead, rather than with usu. Excel's helpful cell adjustments/increments?
    By StargateFan in forum Excel General
    Replies: 3
    Last Post: 11-05-2005, 12:10 PM
  7. [SOLVED] copying a formula, the reference adjusts, but the result does not
    By chezoo in forum Excel General
    Replies: 1
    Last Post: 01-11-2005, 10:06 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1