Offset() returns reference, first not value (proof)
 

FWIW:

In cells B10:D10 I have 111, 222, 333
In Cell D4 I have 3
In cell D6 I have =sum(b10:Offset(b10,0, D4-1))
which displays 666 << which is correct.

But if in the formula bar (on cell D6) if I highlight
(evaluate) the portion Offset(b10,0, D4-1) and
press F9 it equates to 333
Showing =sum(b10:333) << Which of course is not
a valid formula
So "D10" is being returned, versus the Value of D10

Just thought I'd pass along this recent (todays) enlightenment to those in
the group that didn't already know..

Offset() returns reference, first not value (proof)
 

is not Offset(b10,0, D4-1) means the second cell to the right of B10 in the
same row

i.e B10 is origin. the next 0 shows same row. d4-1=2 so thes second cell to
the right.which is d10 i.e. 333

or am I confused????

when you said sum(.....) means sum of values from B10 to d10

=================

"Jim May" wrote in message
news:_qp6f.5038$AO5.3967@dukeread01...
FWIW:

In cells B10:D10 I have 111, 222, 333
In Cell D4 I have 3
In cell D6 I have =sum(b10:Offset(b10,0, D4-1))
which displays 666 << which is correct.

But if in the formula bar (on cell D6) if I highlight
(evaluate) the portion Offset(b10,0, D4-1) and
press F9 it equates to 333
Showing =sum(b10:333) << Which of course is not
a valid formula
So "D10" is being returned, versus the Value of D10

Just thought I'd pass along this recent (todays) enlightenment to those in
the group that didn't already know..

Offset() returns reference, first not value (proof)
 

sorry to confuse
just wanted to point out that
formula displays 666 << which is correct
and underlying formula is:
=sum(B10:D10) << which is correct
not
=sum(B10:333) "the 333 part" which Evaluate() produces << and cell will not
accept entry, of course!

HTH


"R.VENKATARAMAN" wrote in message
...
is not Offset(b10,0, D4-1) means the second cell to the right of B10 in
the
same row

i.e B10 is origin. the next 0 shows same row. d4-1=2 so thes second cell
to
the right.which is d10 i.e. 333

or am I confused????

when you said sum(.....) means sum of values from B10 to d10

=================

"Jim May" wrote in message
news:_qp6f.5038$AO5.3967@dukeread01...
FWIW:

In cells B10:D10 I have 111, 222, 333
In Cell D4 I have 3
In cell D6 I have =sum(b10:Offset(b10,0, D4-1))
which displays 666 << which is correct.

But if in the formula bar (on cell D6) if I highlight
(evaluate) the portion Offset(b10,0, D4-1) and
press F9 it equates to 333
Showing =sum(b10:333) << Which of course is not
a valid formula
So "D10" is being returned, versus the Value of D10

Just thought I'd pass along this recent (todays) enlightenment to those
in
the group that didn't already know..