Looking at your post again it seems that you want to stay on offset 1 so the
formula should be

J4: =OFFSET($B$4,1,0,1,1)

and copied down. However, if you want to change the lookup value in the
future you could have the value in a cell, say E3 and use the formula

J4: =OFFSET($B$4,E3,0,1,1)

Then you can enter a new number in E3 to change the offset value.

Peter
"H.C. Chen" wrote:

You have set the Height of three so when the formula is copied it will come
to
empty cells and produce an error.

But there's no empty cells in the Height of three! I make it clearer by this
example :
B4:B11 = {"data";11;22;33;44;55;66;77}
J4 =OFFSET($B$4,1,0,3,1) = #VALUE! <-------- 11 is expected but why error?
J5 =OFFSET($B$4,1,0,3,1) = 11 <--------------- Correct
J6 =OFFSET($B$4,1,0,3,1) = 22 <----- I think it should be still 11, but why
22?


--
事情不耐做，問題不耐看。


"Billy Liddel" wrote:

Offset uses a single cell as the reference. When You use a range name for the
reference then Excel uses the first cell as the reference. You have set the
Height of three so when the formula is copied it will come to empty cells and
produce an error.

Using the same data 1 to 8 in cells B4:B11 the following formula produces
the sum of the next three rows. Note: The reference row is not absolute.

=SUM(OFFSET($B4,0,0,3))

Not value is returned and the totals wil decrease when there is no value in
the cell covered by the Height. Extend up and down and the value will
eventually be zero.

HTH
Peter Atherton


"H.C. Chen" wrote:

1. Name="Data" B4:B11 = {"data", 1,2,3,4,5,6,7}
2. formula =OFFSET(Data,1,0,3,1) 's value is 2 when at E6 , seems fine
3. But same formula becomes an error #VALUE! when at E5

Why different result happens at E5 and E6 with same formula? I think
=OFFSET(Data,1,0,3,1) has nothing to do with its own position.

--
事情不耐做，問題不耐看。