+ Reply to Thread
Results 1 to 6 of 6

Issues with Formula and AutoFill

  1. #1
    Registered User
    Join Date
    03-02-2017
    Location
    Washington, DC
    MS-Off Ver
    2013
    Posts
    4

    Question Issues with Formula and AutoFill

    Hi All,

    I am trying to re-organize information from one sheet to another. However, when I try and autofill once I've put in a few formulas, the autofill will skip a number of cells from the first sheet. It looks something like this -

    ='Sheet1'!K2&" "&'Sheet1'!L2
    ='Sheet1'!K2&" "&'Sheet1'!L2
    ='Sheet1'!K3&" "&'Sheet1'!L3
    ='Sheet1'!K3&" "&'Sheet1'!L3

    Then when I try and auto fill down it will jump to:

    ='Sheet1'!K6&" "&'Sheet1'!L6
    ='Sheet1'!K6&" "&'Sheet1'!L6
    ='Sheet1'!K7&" "&'Sheet1'!L7
    ='Sheet1'!K7&" "&'Sheet1'!L7

    How can I get it to consistently copy down so that it is K2, K2, K3, K3, K4, K4 and not skip some of those cells? Any help would be hugely appreciated!

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,099

    Re: Issues with Formula and AutoFill

    Use this, copied down:

    =OFFSET(Sheet1!$K$2,INT((ROWS($A$2:A2)-1)/2),,,)+OFFSET(Sheet1!$L$2,INT((ROWS($A$2:A2)-1)/2),,,)
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

  3. #3
    Registered User
    Join Date
    03-02-2017
    Location
    Washington, DC
    MS-Off Ver
    2013
    Posts
    4

    Re: Issues with Formula and AutoFill

    Glenn, thanks for that. It seems to work for numbers but not for text. Is there a way to have it register text as well? At the moment I am getting #VALUE.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,099

    Re: Issues with Formula and AutoFill

    JetMc has kindly pointed out taht I misread your original formula. Try this, instead:

    =OFFSET(Sheet1!$K$2,INT((ROWS($A$2:A2)-1)/2),,,)&" "&OFFSET(Sheet1!$L$2,INT((ROWS($A$2:A2)-1)/2),,,)
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-02-2017
    Location
    Washington, DC
    MS-Off Ver
    2013
    Posts
    4

    Re: Issues with Formula and AutoFill

    The other thing, is that it still will jump cells -

    =OFFSET(Sheet1!$K$2,INT((ROWS($A$2:A2)-1)/2),,,)+OFFSET(Sheet1!$L$2,INT((ROWS($A$2:A2)-1)/2),,,)
    =OFFSET(Sheet1!$K$2,INT((ROWS($A$2:A2)-1)/2),,,)+OFFSET(Sheet1!$L$2,INT((ROWS($A$2:A2)-1)/2),,,)
    =OFFSET(Sheet1!$K$2,INT((ROWS($A$2:A4)-1)/2),,,)+OFFSET(Sheet1!$L$2,INT((ROWS($A$2:A4)-1)/2),,,)
    =OFFSET(Sheet1!$K$2,INT((ROWS($A$2:A4)-1)/2),,,)+OFFSET(Sheet1!$L$2,INT((ROWS($A$2:A4)-1)/2),,,)

    But skips A3 which I still need. Any thoughts?

  6. #6
    Registered User
    Join Date
    03-02-2017
    Location
    Washington, DC
    MS-Off Ver
    2013
    Posts
    4

    Re: Issues with Formula and AutoFill

    Nevermind, I didn't see the new post. That works and doesn't skip. Many thanks for your help, really appreciated!

+ 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. Last Row AutoFill Issues
    By ashley72788 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-15-2016, 02:31 PM
  2. [SOLVED] Selection.AutoFill ISSUES
    By batchy in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-26-2014, 10:49 AM
  3. [SOLVED] AutoFill Countifs issues
    By bdrilling33 in forum Excel General
    Replies: 9
    Last Post: 09-04-2014, 10:18 AM
  4. [SOLVED] Autofill Issues- recognizing pattern
    By ryanaskren in forum Excel General
    Replies: 3
    Last Post: 08-28-2014, 09:45 AM
  5. Replies: 0
    Last Post: 09-17-2012, 08:24 AM
  6. Autofill Issues with a Macro
    By W Haymaker in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-29-2009, 05:43 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1