+ Reply to Thread
Results 1 to 6 of 6

Using OFFSET to return every nth cell

  1. #1
    Registered User
    Join Date
    09-07-2010
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Using OFFSET to return every nth cell

    Okay... i am working on a database of leads that I retrieved online. All of the information was posted in column A since it was copied straight from a web page. Using the Phone number as an example... the phone number for every company is seperated by 18 cells in column A.
    I am looking for a fomula that I can apply to the column that I am placing the phone number in (column D for arguments sake) which will reference every 18th cell in column A.
    I have tried the OFFSET formula for this, and it works just fine for the initial reference. The formula I am placing in column D looks like this: =Offset(A34,18,0) This effectively returns the value in cell A52. However, when i try to drag that down to the next cell in column D, it returns the following: =Offset(A35,18,0) when what I want it to return is =Offset(A52,18,0) so that it references cell A69.
    How can I get the formula to skip the 18 lines and then continue the fomula from that 18th line, and not the one below the originally referenced line?
    Does this make sense? Please, someone help me. i have 3 people already racking thier brains on this.

    Thanks.

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

    Re: Using OFFSET to return every nth cell

    A little confused ... you say 18 rows but then stipulate you want the offset from A52 to be 17 rows to A69 rather than 18 (A70)..

    Adopting 18 row increment:

    Using OFFSET

    Please Login or Register  to view this content.
    Using INDEX

    Please Login or Register  to view this content.
    assumes first number is in A52 and first formula is D2 - modify references as appropriate

  3. #3
    Registered User
    Join Date
    09-07-2010
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Using OFFSET to return every nth cell

    WORKED LIKE A CHARM! Thank you very much... and yes, it was A70, not 69. Sorry about the confusion.

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Using OFFSET to return every nth cell

    Question DO;
    I notice, you usually use something like ROWS($D$1:D1) to increment while I usually use ROW(D1). Is there a logical preference to choose one over the other?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

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

    Re: Using OFFSET to return every nth cell

    It's something I've picked up from others (notably Aladin Akyurek) - his point was always that the use of ROW meant you were perhaps more prone to future errors depending on the location of your data and how it may alter over time.

    Let's say for example I have 10 numeric values listed in Sheet2!A1:A10

    Now let's say I want to return those 10 values in Sheet1!A1:A10 in desc order (we will run a ROW version in A and ROWS version in B)

    A1: =LARGE(Sheet2!$A$1:$A$10,ROW())
    copied down to A10

    B1: =LARGE(Sheet2!$A$1:$A$10,ROWS(B$1:B1))
    copied down to B10

    Now let's say I belatedly realise that I want to add a header row on Sheet1 so I insert a new row 1 and enter my headings.

    You will now of course find that though Col B is still reporting the correct values whereas Col A is not ... the formula in A now requires an adjustment to the ROW() of -1

    ROWS therefore can be seen as a more robust version of the same approach.
    That is not to say that using ROW is a big deal and lets remember that sometimes the fact that ROW returns an array can mean it's preferable to ROWS depending on the context
    (eg ROW(A1:A2) actually returns {1;2} whereas ROWS(A1:A2) returns 2)

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Using OFFSET to return every nth cell

    Thanks I got it.

+ 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