Find a Function to use accross different worksheets
 

Hello all,

I am in a bind. I need to discover a function to look at a column in a
series of worksheets to look for a value. If that value is found then
I need the function to return in data in two other cells. For example
in need the function to look in column C for the value X, if Value X is
found then I need the values in column A and B to be returned in another
worksheet. The reason I am need this function is I am trying to cut
down on human error, even though not brain science, on this project.
Please tell me there is function out there that can do this. Thanks
for any information

R. Hale


--
R. Hale
------------------------------------------------------------------------
R. Hale's Profile: http://www.excelforum.com/member.php...o&userid=16823
View this thread: http://www.excelforum.com/showthread...hreadid=320159

Hi
download Morefunc.xll (http://longre.free.fr/english)<and have a look
at THREED for this purpose

--
Regards
Frank Kabel
Frankfurt, Germany

"R. Hale" schrieb im Newsbeitrag
...

Hello all,

I am in a bind. I need to discover a function to look at a column in
a
series of worksheets to look for a value. If that value is found
then
I need the function to return in data in two other cells. For example
in need the function to look in column C for the value X, if Value X
is
found then I need the values in column A and B to be returned in
another
worksheet. The reason I am need this function is I am trying to cut
down on human error, even though not brain science, on this project.
Please tell me there is function out there that can do this. Thanks
for any information

R. Hale


--
R. Hale
---------------------------------------------------------------------
---
R. Hale's Profile:
http://www.excelforum.com/member.php...o&userid=16823
View this thread:
http://www.excelforum.com/showthread...hreadid=320159

"Frank Kabel" wrote...
download Morefunc.xll (http://longre.free.fr/english)<and have a look
at THREED for this purpose
....

Not exactly a complete answer. Using THREED, the formula would need to look
something like

=INDEX(THREED(alpha:omega!A1:B5),MATCH("x",THREED( alpha:omega!C1:C5),0),
{1,2})

But THREED isn't necessary. It's possible to use COUNTIF(INDIRECT(...)) to
determine the worksheet containing the first 'x'. If the worksheets to
search were listed in a range named WSList, the array formula

=INDEX(INDIRECT("'"&LOOKUP(2,
1/COUNTIF(INDIRECT("'"&WSList&"'!C1:C5"),"X"),WSList )&"'!A1:B5"),
MATCH("x",INDIRECT("'"&LOOKUP(2,
1/COUNTIF(INDIRECT("'"&WSList&"'!C1:C5"),"X"),WSList )&"'!C1:C5"),0),
{1,2})

Harlan Grove wrote:
"Frank Kabel" wrote...
download Morefunc.xll (http://longre.free.fr/english)<and have a
look
at THREED for this purpose
...

Not exactly a complete answer. Using THREED, the formula would need
to look something like


=INDEX(THREED(alpha:omega!A1:B5),MATCH("x",THREED( alpha:omega!C1:C5),0)
,
{1,2})

Thanks for completing this. Probably a little bit too lazy on my side.


But THREED isn't necessary. It's possible to use
COUNTIF(INDIRECT(...)) to determine the worksheet containing the
first 'x'. If the worksheets to search were listed in a range named
WSList, the array formula

=INDEX(INDIRECT("'"&LOOKUP(2,
1/COUNTIF(INDIRECT("'"&WSList&"'!C1:C5"),"X"),WSList )&"'!A1:B5"),
MATCH("x",INDIRECT("'"&LOOKUP(2,
1/COUNTIF(INDIRECT("'"&WSList&"'!C1:C5"),"X"),WSList )&"'!C1:C5"),0),
{1,2})

VERY nice. Thought about this approach which you presented some time
ago for returning the worksheet name of the first match.

Frank