The #VALUE error happens if there is data to look at more than 21
columns the right.
No idea why this would be a problem.

RBS


wrote:
That simplifies it a bit too much as there can be other data that I am
not interested in
more to the right.
This one works, with the formula's in column H:

=IF(COUNTA(I3:OFFSET(I3,0,COUNTIF($2:$2,"44P*")))<
6,"NA",OFFSET(H3,0,COUNTA(I3:OFFSET(I3,0,COUNTIF($ 2:$2,"44P*")))) -
OFFSET(H3,0,COUNTA(I3:OFFSET(I3,0,COUNTIF($2:$2,"4 4P*")))-3))

Although I get quite a few #VALUE errors that I don't understand yet.
It is not caused by the data it is looking at as that is fine.

RBS


Bob Phillips wrote:
Bart,

I may be simplifying this too much, but couldn't you get the last item in a
row with

=INDEX(8:8,,COUNTA($7:$7))

where row 7 is the headings, Term, date Value

the previous would then be

=INDEX(8:8,,COUNTA($7:$7)-3)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RB Smissaert" wrote in message
...
In the example it was cells H4.
Not figured this out yet.

What I am trying to do is this:
In a sheet is data with repeating columns in groups of 3 like this:
Term, Date, Value, Term, Date, Value etc.
The only way to see how far to the right this goes is by looking at
fields,
which are in row 2.
This is the 3Test_3 bit.
If in one row there are more than one of the Term, Date, Value groups I
need
to take the last Value
(the one most to the right) and take of the Value in the group to the left
of that.
So for example if I have:
44P.., 05/10/2005, 10, 44P.., 05/10/2006, 8
The result would be 2 (10 - 8)
More complex than I thought, but I will figure it out.

RBS

"RB Smissaert" wrote in message
...
There still is a problem and that is caused by circular references.
My formula will have to look like this:

=IF(COUNTA(OFFSET(I4,0,0,1,COUNTIF($3:$3,"3Test_3* ")))<
4,"NA",OFFSET(H4,0,COUNTA(OFFSET(I4,0,0,1,COUNTIF( $3:$3,"3Test_3*")))) -
OFFSET(H4,0,COUNTA(OFFSET(I4,0,0,1,COUNTIF($3:$3," 3Test_3*")))-3))

Not sure what construction I need to avoid an error message. Have tried
with and If(IsError( construction, but
that still has the same problem.

RBS


"Tom Ogilvy" wrote in message
...
=CountA(offset(A7,0,0,1,COUNTIF($2:$2,"3Morb_3*"))


--
Regards,
Tom Ogilvy


"RB Smissaert" wrote in message
...
How in a worksheet formula would I combine these 2:

=COUNTIF($2:$2,"3Morb_3*")
and
=COUNTA(A7:E7)

where the result of the first formula would provide the column number
for the second formula?

So, if the first formula produced 5 the second one would be right.


RBS