Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
R. Hale
 
Posts: n/a
Default 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

  #2   Report Post  
Frank Kabel
 
Posts: n/a
Default

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


  #3   Report Post  
Harlan Grove
 
Posts: n/a
Default

"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})


  #4   Report Post  
Frank Kabel
 
Posts: n/a
Default

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I compare data in two worksheets to find matching cells? Gary Excel Discussion (Misc queries) 4 March 2nd 06 10:04 PM
HELP! How do you--> Lock a set of rows but also link worksheets to FRUSTRATED Excel Discussion (Misc queries) 6 December 29th 04 11:05 PM
Multiple Worksheets and Print Merge function pfe Excel Discussion (Misc queries) 2 December 3rd 04 12:23 AM
Find function Jahunga Excel Worksheet Functions 2 November 22nd 04 04:38 PM
HOW TO USE FIND OR SEARCH FUNCTION TO FILL DATA hsg Excel Worksheet Functions 2 November 18th 04 08:24 AM


All times are GMT +1. The time now is 12:22 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"