OFFSET to find last value

[Kevin M]

TIA all,

I use this regularly, but for some reason, i can't seem
to get it to work in this instance:

I'm on '2001' trying to pull the final value from '2000'
The formula is:
=OFFSET('2000'!$F$1,0,0,COUNTA('2000'!F:F),1)

But it's returning the F1 value instead of the last
value. I went through and looked at the formula step by
step through evaluation and it comes up with:

Step 1=OFFSET('2000'!F1,0,0,60,1) [there are 60 filled
cells]
Step 2=1000
Finish

The only thing i can see possibly wrong with doing this
is that the numbers in F:F are SUM formulas. I did copy
the values and paste them over top to see if that was the
problem, but the formula is still returning the F1 value.
Thanks for the input.

Scratching my head?!

Kevin M

 

[Frank Kabel]

Hi
formula looks o.k. Does hitting F9 change the value?

 

[Kevin M]

Hi Frank thanks for the reply, it isn't set up for manual
calc. it keeps pulling the first value though, no matter
what i've tried. i did it again on the '2001' sheet
referencing the same sheet and it still pulls the first
entry. weird.

Kevin M

-----Original Message-----
Hi
formula looks o.k. Does hitting F9 change the value?

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag news:39d701c4ab0a$523ba600 [email protected]...

TIA all,

I use this regularly, but for some reason, i can't seem
to get it to work in this instance:

I'm on '2001' trying to pull the final value from '2000'
The formula is:
=OFFSET('2000'!$F$1,0,0,COUNTA('2000'!F:F),1)

But it's returning the F1 value instead of the last
value. I went through and looked at the formula step by
step through evaluation and it comes up with:

Step 1=OFFSET('2000'!F1,0,0,60,1) [there are 60 filled
cells]
Step 2=1000
Finish

The only thing i can see possibly wrong with doing this
is that the numbers in F:F are SUM formulas. I did copy
the values and paste them over top to see if that was the
problem, but the formula is still returning the F1 value.
Thanks for the input.

Scratching my head?!

Kevin M

.

 

[Frank Kabel]

Hi
do you want to email me this file

--
Regards
Frank Kabel
Frankfurt, Germany

Hi Frank thanks for the reply, it isn't set up for manual
calc. it keeps pulling the first value though, no matter
what i've tried. i did it again on the '2001' sheet
referencing the same sheet and it still pulls the first
entry. weird.

Kevin M

-----Original Message-----
Hi
formula looks o.k. Does hitting F9 change the value?

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag news:39d701c4ab0a$523ba600 [email protected]...

TIA all,

I use this regularly, but for some reason, i can't seem
to get it to work in this instance:

I'm on '2001' trying to pull the final value from '2000'
The formula is:
=OFFSET('2000'!$F$1,0,0,COUNTA('2000'!F:F),1)

But it's returning the F1 value instead of the last
value. I went through and looked at the formula step by
step through evaluation and it comes up with:

Step 1=OFFSET('2000'!F1,0,0,60,1) [there are 60 filled
cells]
Step 2=1000
Finish

The only thing i can see possibly wrong with doing this
is that the numbers in F:F are SUM formulas. I did copy
the values and paste them over top to see if that was the
problem, but the formula is still returning the F1 value.
Thanks for the input.

Scratching my head?!

Kevin M

.

 

[Domenic]

Try...

=OFFSET('2000'!$F$1,COUNTA('2000'!F:F)-1,0)

If your column contains blank cells, you could try the following
formulas...

For numerical values:

=LOOKUP(9.99999999999999E+307,F:F)

For text values:

=LOOKUP(REPT("z",255),F:F)

If your column can contain either numerical or text values:

=LOOKUP(2,1/(1-ISBLANK(F1:F100)),F1:F100)

Hope this helps!