Trouble with offset formula

[Peter Bernadyne]

Hello,

I am trying to return a unique value into a cell from a large tabl
using two offset criteria as follows:


=OFFSET(I3,COUNT(A:A)-1-E3,8)

The formula is working in cell I3, I wish for cell I3 to return th
value that is COUNT(A:A)-1-E3 rows down and 8 columns across from it.
The column offset is easy, it's always 8 columns away. However, th
row offset formula calculates correctly only for the first row o
observations, but when I copy it down my worksheet, it does no
calculate correctly for the second, third, and so on (which shoul
change because E4 has a different value than E3 and E5 has a differen
value than E4, and so on).

I was pretty sure this formula should work, but it doesn't. Can anyon
tell me why it's not calculating the correct row offset for higher ro
numbers, please?

Any help would be much appreciated!

Thanks,

-Pet

 

[Frank Kabel]

Hi
maybe you mean
=OFFSET($I$3,COUNT(A:A)-1-E3,8)

Otherwise you may give an example of your data and the expected results

 

[Earl Kiosterud]

Pete,

I'm not clear on this. Should all the copied down formulas use E3 (not E4,
E5, etc.)? If so, make that cell reference absolute, $E$3. To do that, in
edit mode or in the formula bar, put the cursor on or next to the E3 cell
reference, then press then F4 key, then Enter. Now copy down again. I have
a feeling that this isn't it, though.

Give us an example of a failing formula. Tell what's in E4 (or whatever
relevant cell), and
what it returns.

 

[Peter Bernadyne]

Thank you for your suggestions. However, as you both advised, I though
it would be better to post my file here, instead.

I have a textbox in my worksheet which explains what I am trying t
do.

Thanks again for all your help!

-Pete

 

[Peter Bernadyne]

Hmmm....not sure if my file made it...

I could e-mail it to you, I suppose

 

[Frank Kabel]

Hi
don't post your file (as it is not recommended to attach files). Simply
copy your current formula and paste it as plain text

 

[Peter Bernadyne]

....Or, here is an image of my worksheet (provided it uploads, o
course).

Thanks again,

-Pete