+ Reply to Thread
Results 1 to 9 of 9

using indirect to get same column, subsequent rows from different sheet

  1. #1
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    using indirect to get same column, subsequent rows from different sheet

    I need to fill a single row on one sheet with table data in second sheet.


    I've attached a sample workbook which illustrates what I want to accomplish.

    Thank You!
    Attached Files Attached Files

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: using indirect to get same column, subsequent rows from different sheet

    =INDEX(Sheet1!$15:$15,(ROW($A1)-1)*7+COLUMN(A$1)) pull to the right and down

  3. #3
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: using indirect to get same column, subsequent rows from different sheet

    Hmm, there shouldn't be any additional rows in Sheet1. We need to fill row 15, from column A until (about) column DX.

    So columns H65:M65 in Sheet 1 should contain the data in cells A3:F3 in sheet2. And repeat to the right (leaving an empty column between these "sets" as you see in Sheet1.

    Does that make sense?

    Thanks!

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: using indirect to get same column, subsequent rows from different sheet

    Try this in Sheet1 A15 and filled right as far as needed.

    =INDEX(Table1,(COLUMN()-1)/7+1,MOD(COLUMN(),7))

  5. #5
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: using indirect to get same column, subsequent rows from different sheet

    Try this formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: using indirect to get same column, subsequent rows from different sheet

    Thanks everyone. I couldn't get either script to work so for now I just plugged in the numbers.

  7. #7
    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,895

    Re: using indirect to get same column, subsequent rows from different sheet

    The solutions at Posts 4 and 5 both seem to be OK??!!
    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

  8. #8
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: using indirect to get same column, subsequent rows from different sheet

    Here's the solution from post 4
    If the error 'between' each section is a problem, just add IFERROR
    =IFERROR(INDEX(Table1,(COLUMN()-1)/7+1,MOD(COLUMN(),7)),"")
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: using indirect to get same column, subsequent rows from different sheet

    Thanks. Yes I think you're right. The spacing column must be what screwed it up.

+ 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. [SOLVED] Recognize certain text in a column and delete subsequent rows automatically
    By Swishmak3r in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-07-2017, 01:23 AM
  2. Replies: 3
    Last Post: 07-23-2015, 04:04 AM
  3. Replies: 2
    Last Post: 10-05-2014, 04:10 PM
  4. Copy the employee Ids to subsequent rows (approx 30 rows) using macro - Roster Type
    By Keerthi Raj in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-21-2013, 01:10 AM
  5. [SOLVED] updating subsequent rows on a different sheet
    By greg19 in forum Excel General
    Replies: 5
    Last Post: 04-09-2013, 09:03 PM
  6. How to use 'indirect' to sum a column on another sheet
    By sumdumgai in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-06-2013, 10:09 AM
  7. update the subsequent rows at each change in other column
    By pavan.dhoke in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 03-28-2011, 05:49 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