Find a Function to use accross different worksheets

[R. Hale]

Hello all,

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

R. Hal

 

[Frank Kabel]

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

 

[Harlan Grove]

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(alphamega!A1:B5),MATCH("x",THREED(alphamega!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})

 

[Frank Kabel]

...

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

=INDEX(THREED(alphamega!A1:B5),MATCH("x",THREED(alphamega!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