+ Reply to Thread
Results 1 to 6 of 6

Looking for formula to copy down that increments the cell reference 5 rows each time

  1. 01-23-2018, 04:45 PM #1
    Cbird
    Cbird is offline
    Registered User
    Join Date
    10-24-2017
    Location
    Omaha, NE
    MS-Off Ver
    Excel 2010
    Posts
    25

    Looking for formula to copy down that increments the cell reference 5 rows each time

    In Worksheet A, cell A3 I have the following formula: ='[File B.xlsx]Spreadsheet B'!$C5
    In Worksheet A, cell A4 I would like to return the value that is ='[File B.xlsx]Spreadsheet B'!$C10
    In Worksheet A , cell A5 I would like to return the value that is ='[File B.xlsx]Spreadsheet B'!$C15
    This pattern will continue for many rows, incrementing 5 rows each time.

    Looking for a way to write a formula I can copy down, so that I don't have to manually copy
    and edit the cell reference on each line. Just not sure which excel function may help me do this.

    Thank you for your assistance.
    Reply With QuoteRegister To Reply
  2. 01-23-2018, 05:09 PM #2
    Estevaoba
    Estevaoba is offline
    Forum Expert
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365
    Posts
    1,249

    Re: Looking for formula to copy down that increments the cell reference 5 rows each time

    Try this in A3 and copy down.

    =OFFSET('[File B.xlsx]Spreadsheet B'!$C$5,(ROW(A1)-ROW(A$1))*5,0)
    Reply With QuoteRegister To Reply
  3. 01-23-2018, 05:31 PM #3
    ron2k_1
    ron2k_1 is offline
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: Looking for formula to copy down that increments the cell reference 5 rows each time

    Offset doesn't work in closed workbook. You'll have to use INDIRECT. Give us the full file path and we'll give you a formula but bear in mind that this formula will really slow down your file if you have to many of them.
    Ron
    Knowledge is knowing that a tomato and pepper are fruits. Wisdom is knowing whether to put these in a fruit salad

    Kindly

    [1] Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
    [2] Mark your post [SOLVED] if it has been answered satisfactorily by editing your original post in advanced mode.
    [3] Thank (using the little scale) those that provided useful help; its nice and its very well appreciated
    Reply With QuoteRegister To Reply
  4. 01-23-2018, 05:42 PM #4
    ron2k_1
    ron2k_1 is offline
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: Looking for formula to copy down that increments the cell reference 5 rows each time

    What am I thinking. Don't use INDIRECT. Here you go and drag down; make sure File B is open when pasting the formula:
    =INDEX('[File B.xlsx]Spreadsheet B'!$C$1:$C$5000,(ROW()-2)*5,)

    The number 2 highlighted in red needs to adjusted [in the first formula only] so that the row in which the formula is placed gives you row 1 as your starting point. So, in your case you say that the formula will be placed in A3 (so subtract 2 to get to row 1). If your first formula will be in A5, then subtract 4 to get to row 1...
    Reply With QuoteRegister To Reply
  5. 01-24-2018, 02:10 PM #5
    Cbird
    Cbird is offline
    Registered User
    Join Date
    10-24-2017
    Location
    Omaha, NE
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Looking for formula to copy down that increments the cell reference 5 rows each time

    Thank you - the posts were very helpful. I was able to apply the formula along with using indirect to solve my problem.
    Reply With QuoteRegister To Reply
  6. 01-24-2018, 02:16 PM #6
    Glenn Kennedy
    Glenn Kennedy is offline
    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
    43,984

    Re: Looking for formula to copy down that increments the cell reference 5 rows each time

    But don't forget that INDIRECT will ONLY work when both workbooks are OPEN
    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.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    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. Macro to copy and paste a row of formula down by x rows where x is in a reference cell
    By davidx in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-21-2015, 12:59 PM
  2. Time Increments (summing value) - need help wih formula
    By auswtz in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-30-2013, 08:52 PM
  3. [SOLVED] Copy Cell Reference formula to 3 rows down but increment formula by only 1 row
    By jwnickman in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-23-2013, 01:57 PM
  4. [SOLVED] a way to copy/paste formula every 7th column that increments formula reference by 1
    By merlyn45 in forum Excel General
    Replies: 7
    Last Post: 11-12-2012, 02:46 PM
  5. cannot copy formula to below cell with numerical increase in increments of 1
    By Special.Agent in forum Excel General
    Replies: 7
    Last Post: 12-16-2011, 08:41 AM
  6. Copy/Paste Formula that increments page names and pulls data from same cell
    By austinrob in forum Excel General
    Replies: 5
    Last Post: 01-27-2010, 05:22 PM
  7. increase a cell reference by increments greater than one...
    By Gary Fuller in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-14-2005, 03:06 PM

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