+ Reply to Thread
Results 1 to 5 of 5

I want to use a cell number then get data from a cell 12 rows below the original cell

  1. #1
    Registered User
    Join Date
    05-13-2009
    Location
    Surfers Paradise, Australia
    MS-Off Ver
    Excel 2007
    Posts
    2

    Unhappy I want to use a cell number then get data from a cell 12 rows below the original cell

    I have a database that I need to clean up.
    I don't want to go through and delete all the empty rows as it will take me way too much time.
    I have a 2 sheet spreadsheet. Sheet 1 has my raw database (names, address, phone number, etc). Sheet 2 will be used to collect data from sheet 1.

    In sheet2 a2 i have done =Sheet1!A3 which returns the data 'John Citezen' (this is an example and not a real name), all good.

    The next data I want to put in Sheet2 A3 is 12 cells below Sheet1 A3 (continues 24 cells below, 36cells below,48 cells below,etc). I don't know what formula to put in so that it will find my original cell (Sheet1A3) then bring back the data which is 12 rows below that cell.

    I want to be able to put a formula in Sheet2 cell A3 which will bring the data from Sheet1!A15, then be able to drag that down to complete my data collection.

    I have tried this in sheet2:
    In Sheet2 cell A2 I have put the formula =Sheet1!A3 - This returns the persons name
    In Sheet2 cell A3 I have put the formula=Sheet1!A15 - This returns the 2nd persons name
    When i Highlight Sheet2 a2 & a3 and fill down, the following appears in Sheet 2 cell A4 --> =Sheet1!A5 which is not what i want. I want it to be =Sheet1!A27 --> and I want sheet 2 cell A5 to be =Sheet1!A39 and so on.

    Thanks in advance for your time.

  2. #2
    Forum Contributor
    Join Date
    04-01-2009
    Location
    Irvine, CA
    MS-Off Ver
    Excel 2010
    Posts
    280

    Re: I want to use a cell number then get data from a cell 12 rows below the original

    It's not pretty, but it works in A2 on down

    =INDIRECT("Sheet1!A" & ((ROW()-2)*12)+3)

  3. #3
    Registered User
    Join Date
    02-12-2009
    Location
    Internets
    MS-Off Ver
    Business
    Posts
    1

    Re: I want to use a cell number then get data from a cell 12 rows below the original

    Quote Originally Posted by Chance2 View Post
    It's not pretty, but it works in A2 on down

    =INDIRECT("Sheet1!A" & ((ROW()-2)*12)+3)
    Thank you, opens up a lot of possibilities

  4. #4
    Registered User
    Join Date
    05-13-2009
    Location
    Surfers Paradise, Australia
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: I want to use a cell number then get data from a cell 12 rows below the original

    Thanks Chance2
    Worked perfectly, but you already knew that didn't you?
    I've modified it to gather all the other info (address, mobile number, email,etc) in the same fashion.

    Thanks for your time.

    (great experience from my first ever post)

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: I want to use a cell number then get data from a cell 12 rows below the original

    INDIRECT is Volatile and as such is best avoided wherever and whenever possible, you can in this instance make use of a standard INDEX function (also a little more flexible):

    Sheet2!
    A2: =INDEX(Sheet1!A:A,3+(12*(ROWS(A$2:A2)-1)))
    copy down as required and across to get different columns

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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