hi,

i have this formula that i have set up but i dont know if i am going down
the wrong route or its just the final bit i cannot work out....
i am trying to view the value in coloum 'I' when the first value in coloum
'B' is found....
there will (or should) only be one value of coloum 'B' as this is a list of
names which gets generated each day. how to i view the value it brings up in
coloum 'I'?

=SUMPRODUCT(--('[Reporting
10-01-04.xls]Sheet2'!B$2:$B1000=B1),--('[Reporting
10-01-04.xls]Sheet2'!$I$2:$I1000=" "))

thank you in advance!

If you want to return a value from column I:
=SUMPRODUCT(--('[Reporting 10-01-04.xls]Sheet2'!B$2:$B1000=B1), '[Reporting
10-01-04.xls]Sheet2'!$I$2:$I1000)
I think your formula try to count how many times the name in B occurred in
the table when the I column had 6 (?) blanks in it.

Have you tired SUMIF or INDEX/MATCH ?
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Steve" wrote in message
...
hi,

i have this formula that i have set up but i dont know if i am going down
the wrong route or its just the final bit i cannot work out....
i am trying to view the value in coloum 'I' when the first value in coloum
'B' is found....
there will (or should) only be one value of coloum 'B' as this is a list
of
names which gets generated each day. how to i view the value it brings up
in
coloum 'I'?

=SUMPRODUCT(--('[Reporting
10-01-04.xls]Sheet2'!B$2:$B1000=B1),--('[Reporting
10-01-04.xls]Sheet2'!$I$2:$I1000=" "))

thank you in advance!

It sounds more like you want a vlookup or IMO better still an Index Match
formula.

=index('[Reporting 10-01-04.xls]Sheet2'!$I$2:$I1000, match(B1, '[Reporting
10-01-04.xls]Sheet2'!$B$2:$B1000, 0))
--
HTH...

Jim Thomlinson


"Steve" wrote:

hi,

i have this formula that i have set up but i dont know if i am going down
the wrong route or its just the final bit i cannot work out....
i am trying to view the value in coloum 'I' when the first value in coloum
'B' is found....
there will (or should) only be one value of coloum 'B' as this is a list of
names which gets generated each day. how to i view the value it brings up in
coloum 'I'?

=SUMPRODUCT(--('[Reporting
10-01-04.xls]Sheet2'!B$2:$B1000=B1),--('[Reporting
10-01-04.xls]Sheet2'!$I$2:$I1000=" "))

thank you in advance!

It sounds like you want a "lookup" formula rather than a "counting" formula.

See if this does what you want:

=INDEX('[Reporting 10-01-04.xls]Sheet2'!$I$2:$I$1000,MATCH(B1,'[Reporting
10-01-04.xls]Sheet2'!$B$2:$B$1000,0))

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
hi,

i have this formula that i have set up but i dont know if i am going down
the wrong route or its just the final bit i cannot work out....
i am trying to view the value in coloum 'I' when the first value in coloum
'B' is found....
there will (or should) only be one value of coloum 'B' as this is a list
of
names which gets generated each day. how to i view the value it brings up
in
coloum 'I'?

=SUMPRODUCT(--('[Reporting
10-01-04.xls]Sheet2'!B$2:$B1000=B1),--('[Reporting
10-01-04.xls]Sheet2'!$I$2:$I1000=" "))

thank you in advance!

SUMIF example:
=SUMIF([Book2]Sheet2!$B$2:$B$1000,B1,[Book2]Sheet2!$I$2:$I$1000)
INDEX/MATCH example"
=INDEX([Book2]Sheet2!$I$2:$I$1000,MATCH(B1,[Book2]Sheet2!$B$2:$B$1000,0))

best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Steve" wrote in message
...
hi,

i have this formula that i have set up but i dont know if i am going down
the wrong route or its just the final bit i cannot work out....
i am trying to view the value in coloum 'I' when the first value in coloum
'B' is found....
there will (or should) only be one value of coloum 'B' as this is a list
of
names which gets generated each day. how to i view the value it brings up
in
coloum 'I'?

=SUMPRODUCT(--('[Reporting
10-01-04.xls]Sheet2'!B$2:$B1000=B1),--('[Reporting
10-01-04.xls]Sheet2'!$I$2:$I1000=" "))

thank you in advance!

It sounds like you want a "lookup" formula rather
than a "counting" formula.

Although, SUMPRODUCT can be used as a "lookup" formula *IF* the data to be
returned is numeric and the "lookup value" is unique.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
It sounds like you want a "lookup" formula rather than a "counting"
formula.

See if this does what you want:

=INDEX('[Reporting 10-01-04.xls]Sheet2'!$I$2:$I$1000,MATCH(B1,'[Reporting
10-01-04.xls]Sheet2'!$B$2:$B$1000,0))

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
hi,

i have this formula that i have set up but i dont know if i am going down
the wrong route or its just the final bit i cannot work out....
i am trying to view the value in coloum 'I' when the first value in
coloum
'B' is found....
there will (or should) only be one value of coloum 'B' as this is a list
of
names which gets generated each day. how to i view the value it brings up
in
coloum 'I'?

=SUMPRODUCT(--('[Reporting
10-01-04.xls]Sheet2'!B$2:$B1000=B1),--('[Reporting
10-01-04.xls]Sheet2'!$I$2:$I1000=" "))

thank you in advance!