+ Reply to Thread
Results 1 to 6 of 6

sheet1 row with spaces between cells, pulled to sheet2 column with no spaces between cells

  1. #1
    Registered User
    Join Date
    03-12-2013
    Location
    chester, va
    MS-Off Ver
    Excel 2010
    Posts
    52

    sheet1 row with spaces between cells, pulled to sheet2 column with no spaces between cells

    i've found this equation
    =IFERROR(INDEX(Sheet1!$A$1:$Z$1,SMALL(INDEX(Sheet1!$A$1:$Z$1<>"",0)*COLUMN(Sheet1!$A$1:$Z$1),COLUMN(Sheet1!A$1)+COUNTBLANK(Sheet1!$A$1:$Z$1))),"")
    and that lets me pull data from the row on sheet1 to sheet2, excluding all the empty cells.
    i however just need every third cell pulled from the row on sheet1 to be put into the column on sheet2.
    using equations like this
    =IF(MOD(COLUMN(A1),4)=1, INDEX(Main!$A3:$A998, CEILING(COLUMN(A1)/4, 1)),"")
    allowed me to pull info from a column on one sheet to a row on another while leaving four blanks inbetween the next data entry
    i guess what i need is this reversed.
    ceiling and floor functions seem to pertain to rounding so i'm not sure how that works here but it definietly does.
    i tried switching columns to rows and such and no luck obviously.
    any insight is greatly appreicated. thanks

    sheet1 starting cell is m13 (row)
    sheet2 starting cell is am3 (column)

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: sheet1 row with spaces between cells, pulled to sheet2 column with no spaces between c

    Quote Originally Posted by chestersneakers7 View Post
    i however just need every third cell pulled from the row on sheet1 to be put into the column on sheet2.
    Tell us what a few of the cell addresses are in the row and tell us the address of the first cell where the results are to appear.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    03-12-2013
    Location
    chester, va
    MS-Off Ver
    Excel 2010
    Posts
    52

    Re: sheet1 row with spaces between cells, pulled to sheet2 column with no spaces between c

    info needs to come from sheet1 to sheet2

    sheet1 m13 = sheet2 am3
    sheet1 p13 = sheet2 am4
    sheet1 s13 = sheet2 am5
    sheet1 v13 = sheet2 am6

    thanks!

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: sheet1 row with spaces between cells, pulled to sheet2 column with no spaces between c

    Enter this formula in Sheet2 AM3 and copy down as needed:

    =INDEX(Sheet1!M$13:IV$13,ROWS(AM$3:AM3)*3-3+1)

    Adjust for the correct end of range where I use up to IV13.

  5. #5
    Registered User
    Join Date
    03-12-2013
    Location
    chester, va
    MS-Off Ver
    Excel 2010
    Posts
    52

    Re: sheet1 row with spaces between cells, pulled to sheet2 column with no spaces between c

    Thanks so much man!

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: sheet1 row with spaces between cells, pulled to sheet2 column with no spaces between c

    You're welcome!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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