how to lookup in numerous different sheets

[Zak]

Hi,

I am trying to lookup the value of one column in one worksheet in another
workbook that has about 20 sheets. I know how to do a lookup seperately in
each sheet but is there not a way that I can just do the one lookup from the
one sheet which will search the whole workbook (looking into each of the 20
sheets) and return what i am looking for. Essentially i just need to know if
the people on this one column are presesnt in any of the 20 sheets in another
workbook.

Thanks.

 

[Jim Rech]

is there not a way that I can just do the one lookup

The Excel lookup worksheet functions all search one range on one sheet so
you'd need multiple lookups.

--
Jim
| Hi,
|
| I am trying to lookup the value of one column in one worksheet in another
| workbook that has about 20 sheets. I know how to do a lookup seperately in
| each sheet but is there not a way that I can just do the one lookup from
the
| one sheet which will search the whole workbook (looking into each of the
20
| sheets) and return what i am looking for. Essentially i just need to know
if
| the people on this one column are presesnt in any of the 20 sheets in
another
| workbook.
|
| Thanks.

 

[Zak]

OK, if the lookup function wont do what i want it to is there a macro that
can do the job? do you have a code?

any help would be much appreciated.

 

[Jim Rech]

No I don't have such a macro. It would probably be highly specific to your
exact situation so you would have to write it yourself or get someone you
work with to do it.

--
Jim
| OK, if the lookup function wont do what i want it to is there a macro that
| can do the job? do you have a code?
|
| any help would be much appreciated.
|
| "Jim Rech" wrote:
|
| > >>is there not a way that I can just do the one lookup
| >
| > The Excel lookup worksheet functions all search one range on one sheet
so
| > you'd need multiple lookups.
| >
| > --
| > Jim
| > | > | Hi,
| > |
| > | I am trying to lookup the value of one column in one worksheet in
another
| > | workbook that has about 20 sheets. I know how to do a lookup
seperately in
| > | each sheet but is there not a way that I can just do the one lookup
from
| > the
| > | one sheet which will search the whole workbook (looking into each of
the
| > 20
| > | sheets) and return what i am looking for. Essentially i just need to
know
| > if
| > | the people on this one column are presesnt in any of the 20 sheets in
| > another
| > | workbook.
| > |
| > | Thanks.
| >
| >
| >

 

[Zak]

Can you not offer me any guidance as to how to write it? i mean i know what
to do but dont really know how to get around it..

thanks in advance.

 

[Jim Cone]

An overview of what is required is ...
Loop thru the twenty sheets. (ActiveWindow.SelectedSheets)
On each sheet, use the Find function ( in another loop ) to search for the text.
When an item is found, identify the row number and sheet name and list it
on another sheet ...

It took about 700 lines of code for me to accomplish the above, except the
entire row containing the found stuff is show on a separate sheet.
Also, one can enter multiple items to search for.

You can download the free trial of XL Companion from my website and give
the "Find and List" utility a try to see how successful? it is.
Maybe it will do what you want.
Comments and suggestions about the program are welcomed...
http://www.realezsites.com/bus/primitivesoftware
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Zak"
wrote in message
Can you not offer me any guidance as to how to write it? i mean i know what
to do but dont really know how to get around it..

thanks in advance.

 

[Zak]

i sent a reply but i dont think it got sent!

do you think it would be easier for me to work through each step and record
a macro while i do it? i.e. lookup in each sheet then create a new sheet to
input the outcome? but this may not be easy because lookup cells and number
of cells wil change all the time, consequently making my formula
ineffective..? how do i cater for changes?

do you not have a code i can work through easily? sorry to be such a pain
but i am really finding this difficult!

thanks for all your help.