OFFSET returns a value from a known cell that is a certain number of rows and columns away. As an experiment, enter a number in A10 on a worksheet that you can experiment on. Now, in another cell (anywhere) enter this formula =OFFSET(A1,9,0,1,1) The value that you entered in A10 should be returned. What the formula says is something like this. Starting in A1 count down 9 rows, stay in the same column and return the value in the range 1 row high and 1 column wide. This formula can be shortened down to =OFFSET(A1,9,0) as the height and width of the reference are not relevant in this case.

Now, with the same value in A10 enter this formula in another cell =OFFSET(B20,-10,-1) This means to start at cell B20, go up 10 rows and to the left 1 column.

=OFFSET($I$6,0,0,COUNTA($I$6:$I$50),1) This form of the OFFSET is used to create dynamic ranges. That is, ranges of varying length which is useful with creating drop down lists and you don't want a big space at the bottom of the list or you want the drop down list to expand when new items are added to it without having to change the cell references to the range in the Name Manager. This formula says to start at $I$6 (a fixed cell),don't move any rows or columns, (the two zeros) but make the range the size of the count of cells that are not empty and keep the range 1 column wide. The range specified in the COUNTA part puts a limit on the amount of data that can be entered in the list without having to re-define the list range. If there was going to be more than 50 items the 50 could be any number that would be sufficient to satisfy you.