Results 1 to 14 of 14

Thread: Using OFFSET

  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Using OFFSET

    Hi all....I am having some trouble with OFFSET....I think that I know what it is supposed to do, and it seems as if ti could be a very useful formula...however, I cannot make it work with any regularity, and I suspect it is b/c I don't fully understand how it works. I have attached a simple example showing 4 names (and ages) on sheet1. On sheet2, I have 3 examples of how I have tried to set up OFFSET.....the formula is written somewhat differently b/c the Excel help files 'explain' it in a way that I don't understand (and that produces a formula that is different than I have seen in other examples). I want to use it to replicate only the names in adjacent columns (or rows) on sheet2 but without reference to the 'age' columns b/w the names on sheet1. I can fill in the formulas that I have but that means going back and editing each one to refer to every-second-column............is there an OFFSET formula that will skip the 'age' columns on sheet1?
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could you elaborate on what you want the output to be?

    Steve

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    In this case, the output should be all the names from sheet1 to be copied to sheet2, in either 1 column in adjacent rows, or or a single row in adjacent columns (skipping the 'age' data)....however, doesn't that depend on how the OFFSET formula is structured?

  4. #4
    2 Star Lounger
    Join Date
    Mar 2010
    Location
    Tampa, FL, USA
    Posts
    114
    Thanks
    11
    Thanked 10 Times in 9 Posts
    I'm not entirely clear the output you want as each of the three examples shows a different example. However, to get the items listed by skipping every other column, then:

    1. Using helper row if the output will span columns:
    a. Add row above the names, with "0" above the 1st name, 1 above the second, etc.
    b. With the "0" in e.g. cell D15 (see my attached sheet), in the cell in the row immediately under "0" use the command
    Code:
    =OFFSET(Sheet1!$B$8,0,2*D15)
    Note: If you define a name (e.g. "firstname") which refers to =Sheet1!$B$8, then the above statement is more readable and reads:
    Code:
    =OFFSET(firstname,0,2*D15)
    2. Using helper column if the output will span rows is essentially the same, with the 0 beside the location of the first name. Using B20 for the "0" cell, and the names starting at C20, the formula in C20, using the defined name above, will be:
    Code:
    =OFFSET(firstname,0,2*B18)
    The updated sheet with the formula and helper areas highlighted is attached.
    Attached Files Attached Files
    PJ in FL

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Thank you for this pj....it seems to make using OFFSET fairly straightforward, altho I don't understand what or how the 'helper row' does its thing...??..??

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The helper row defines the column to get from the source. YOu could get rid of it and base in on the column of the cell [column()] but that would have to be adjusted by what column the first item was in. In D16:
    =OFFSET(firstname,0,2*(COLUMN()-COLUMN($D$16)))
    Copy this to E16 to G16

    or in C18:
    =OFFSET(firstname,0,2*(ROW()-ROW($C$18)))
    Copy this to from C19 to C21

    Of course change the $D$16 and the $C$18 to the first formula in the range if you put the formula in a different location

    Steve

  7. #7
    2 Star Lounger
    Join Date
    Mar 2010
    Location
    Tampa, FL, USA
    Posts
    114
    Thanks
    11
    Thanked 10 Times in 9 Posts
    The helper row is simply a way to get an incrementing number for OFFSET to use to locate the next entry.

    There are more sophisticated ways to obtain the next nth item, using the current cell's own offset from the first entry in the list to provide the increment, such that the first example which started at D16 would change to
    Code:
    =OFFSET(Sheet1!$B$8,0,2*(COLUMN()-COLUMN($D$16)))
    This formula eliminates the need for the helper row, using the columns themselves to provide the incremental value (times 2 for every other column).

    The example where each subsequent row contained the names from the columns in Sheet1 will be
    Code:
    =OFFSET(Sheet1!$B$8,0,2*(ROW()-ROW($C$18)))
    Select and copy those right or down as needed to get the names from Sheet1.
    PJ in FL

  8. #8
    2 Star Lounger
    Join Date
    Mar 2010
    Location
    Tampa, FL, USA
    Posts
    114
    Thanks
    11
    Thanked 10 Times in 9 Posts
    Yeah, what SDCKAPR said!
    PJ in FL

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Great minds think alike!

    Steve

  10. #10
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    OK..thanks guys, b/c when I look at your examples, the formulas start to resemble formlulas that I have seen, but never in the Excel help-files....I can now dissect them and understand how it works! Thank you

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Snce you are using only 3 of the 5 parameters to essentially extract one value (instead of definging a range), you can look at is as akin to the INDEX function. The only differences are: the index defines the accepted range (not just the upper left cell), and the values for rows/columns start at 1 each for the upper left and can only be positive. Offset has the starting cell as the zeroth row and column and the offset can be positive (down/right) or negative (up/left).

    The last 2 parameters in the Offset define the size of the range: how many rows and how many columns it contains

    Steve

  12. #12
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    H guys....I am still having trouble figuring out OFFSET......when you say "upper left cell", do you mean A1...I attach a very small s/sheet....I want to use OFFSET to have the $$amounts in col B show up in col F,H,J,L,N,P......the helper row thing isn't doing it and I have given up on trying to use it...any suggestions?
    Attached Files Attached Files

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If you want to use the helper row, you can make the helper row match the row of the table:
    F15 = 0, G15 = 0.5, H15 = 1, etc

    Then use in F18:
    =OFFSET($B$5,F15,0)

    Copy to H18, J18, etc
    If you want to use the helper row values you have in the example file, then you need the offset formula to convert the helper row information to match the lookup. In F18:
    =OFFSET($B$5,(F15-1)/2,0)

    If you want to NOT use the helper row at all, you can use the COLUMN function to convert to offset row value. In F18:
    =OFFSET($B$5,(COLUMN()-6)/2,0)

    Essentially the lookup subtracts to make the first value equal to an offset of zero(0), and you divide by 2 since your lookup table goes down by 1 row each, but where you want the numbers goes up by 2 each time.

    Steve

  14. #14
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Thank you very much for this Steve....dissecting the various examples has shown me how OFFSET works and that it can be used in different ways....I will applythis to my current project and get back to you...thanks, again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •