How can I have Excel search for specific data, such as a person's first and
last name, through a range of cells in a range of worksheets in the same
workbook? And, how can the results of that search be figured in a separate
worksheet?

More information is needed about the layout of the information in the
worksheets with the names (are names all in a single cell as [Firstname
Lastname] or are they in separate cells as [Firstname] [LastName]). Also
what are you thinking of in terms of "results" of the searches? Count of the
occurances of a given name, or the names and associated information? Can the
same name(s) appear more than once on any given worksheet? That kind of
thing.

Bottom line answer is this can be done - whether it can be done easily with
worksheet formulas or needs to go on to VBA code to accomplish has yet to be
determined.


"Charles" wrote:

How can I have Excel search for specific data, such as a person's first and
last name, through a range of cells in a range of worksheets in the same
workbook? And, how can the results of that search be figured in a separate
worksheet?

"Charles" wrote:
How can I have Excel search for specific data, such as a person's first and
last name, through a range of cells in a range of worksheets in the same
workbook? And, how can the results of that search be figured in a separate
worksheet?

Just some thoughts to this post ..
(Do agree with JLatham that it could have been better described)

I'll assume that you want to create a summary as to where the names are
found in the various source sheets.

Here's a sample construct of the possibilities:
http://www.savefile.com/files/437064
Multiple data search through multiple shts.xls

Assuming the first names and last names would be housed within A2:B10 in
each source sheet (identical structure), col A = first names, col B = last
names

In a sheet: Summary,

Assuming the master list of first names and last names are in cols A and B,
from row2 down

List the source sheetnames in C1 across, eg: Sheet1, Sheet2, etc

Then array-enter with CTRL+SHIFT+ENTER in C2:
=IF(ISNUMBER(MATCH(1,(INDIRECT("'"&C$1&"'!A2:A10") =$A2)*(INDIRECT("'"&C$1&"'!B2:B10")=$B2),0)),"Y"," ")
Copy C2 across & fill down to populate. Where the names are found, "Y" would
be indicated.

And as a possible extension to the above, we could also create a "summary
cum hyperlinks" as to where the names are found.

In another sheet: Summary cum hyperlink,

Assuming the master list of first names and last names are in cols A and B,
from row2 down (as before)

List the sheetnames in C1 across

Put in C2, array-enter with CTRL+SHIFT+ENTER
(instead of just pressing ENTER):
=IF(ISNUMBER(MATCH(1,(INDIRECT("'"&C$1&"'!A2:A10") =$A2)*(INDIRECT("'"&C$1&"'!B2:B10")=$B2),0)),HYPER LINK("#"&CELL("address",INDIRECT("'"&C$1&"'!"&MATC H(1,(INDIRECT("'"&C$1&"'!A2:A10")=$A2)*(INDIRECT(" '"&C$1&"'!B2:B10")=$B2),0)+1&":"&MATCH(1,(INDIRECT ("'"&C$1&"'!A2:A10")=$A2)*(INDIRECT("'"&C$1&"'!B2: B10")=$B2),0)+1)),$A2&" "&$B2),"")

Copy C2 across & fill down to populate to return a summary matrix of
hyperlinked names. Clicking on the hyperlinks will jump you straight to the
particular source sheet's row where the particular name is located (a useful
feature, no? <g). Just use the back button on the web toolbar to return to
the summary sheet.

Adapt the ranges to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

You're right. I could have explained that better. Trying to put into words
what I need, exactly. Relatively new to Excel and trying to wrap my head
around it. Here's the scenario:

I have a workbook of that encompasses many worksheets. There are eight sets
of worksheets that all feed into a central worksheet. What I mean by set are
12 worksheet on a subject, in this case, the run of a theatrical production.
Each worksheet is a single performance. What the worksheets do is allow for
seating reservations and type of ticket sold. The worksheet is already
programmed to tally each type of ticket sold per a legend and centralize the
results on a single worksheet that serves as an overview of the year. Where
my question comes in is that I also have a listing of my season ticket
holders that can come to one performance per show pre-paid and I want a way
to track them by first and last name in a single cell and transfer that info
to the season ticket worksheet, so that we can better track who attends what
performance and prevent multiple "pre-paid showings". Does this make sense?

"JLatham" wrote:

More information is needed about the layout of the information in the
worksheets with the names (are names all in a single cell as [Firstname
Lastname] or are they in separate cells as [Firstname] [LastName]). Also
what are you thinking of in terms of "results" of the searches? Count of the
occurances of a given name, or the names and associated information? Can the
same name(s) appear more than once on any given worksheet? That kind of
thing.

Bottom line answer is this can be done - whether it can be done easily with
worksheet formulas or needs to go on to VBA code to accomplish has yet to be
determined.


"Charles" wrote:

How can I have Excel search for specific data, such as a person's first and
last name, through a range of cells in a range of worksheets in the same
workbook? And, how can the results of that search be figured in a separate
worksheet?

You're right. I could have explained that better. Trying to put into words
what I need, exactly. Relatively new to Excel and trying to wrap my head
around it. Here's the scenario:

I have a workbook of that encompasses many worksheets. There are eight sets
of worksheets that all feed into a central worksheet. What I mean by set are
12 worksheet on a subject, in this case, the run of a theatrical production.
Each worksheet is a single performance. What the worksheets do is allow for
seating reservations and type of ticket sold. The worksheet is already
programmed to tally each type of ticket sold per a legend and centralize the
results on a single worksheet that serves as an overview of the year. Where
my question comes in is that I also have a listing of my season ticket
holders that can come to one performance per show pre-paid and I want a way
to track them by first and last name in a single cell and transfer that info
to the season ticket worksheet, so that we can better track who attends what
performance and prevent multiple "pre-paid showings". Does this make sense?

"Max" wrote:

"Charles" wrote:
How can I have Excel search for specific data, such as a person's first and
last name, through a range of cells in a range of worksheets in the same
workbook? And, how can the results of that search be figured in a separate
worksheet?

Just some thoughts to this post ..
(Do agree with JLatham that it could have been better described)

I'll assume that you want to create a summary as to where the names are
found in the various source sheets.

Here's a sample construct of the possibilities:
http://www.savefile.com/files/437064
Multiple data search through multiple shts.xls

Assuming the first names and last names would be housed within A2:B10 in
each source sheet (identical structure), col A = first names, col B = last
names

In a sheet: Summary,

Assuming the master list of first names and last names are in cols A and B,
from row2 down

List the source sheetnames in C1 across, eg: Sheet1, Sheet2, etc

Then array-enter with CTRL+SHIFT+ENTER in C2:
=IF(ISNUMBER(MATCH(1,(INDIRECT("'"&C$1&"'!A2:A10") =$A2)*(INDIRECT("'"&C$1&"'!B2:B10")=$B2),0)),"Y"," ")
Copy C2 across & fill down to populate. Where the names are found, "Y" would
be indicated.

And as a possible extension to the above, we could also create a "summary
cum hyperlinks" as to where the names are found.

In another sheet: Summary cum hyperlink,

Assuming the master list of first names and last names are in cols A and B,
from row2 down (as before)

List the sheetnames in C1 across

Put in C2, array-enter with CTRL+SHIFT+ENTER
(instead of just pressing ENTER):
=IF(ISNUMBER(MATCH(1,(INDIRECT("'"&C$1&"'!A2:A10") =$A2)*(INDIRECT("'"&C$1&"'!B2:B10")=$B2),0)),HYPER LINK("#"&CELL("address",INDIRECT("'"&C$1&"'!"&MATC H(1,(INDIRECT("'"&C$1&"'!A2:A10")=$A2)*(INDIRECT(" '"&C$1&"'!B2:B10")=$B2),0)+1&":"&MATCH(1,(INDIRECT ("'"&C$1&"'!A2:A10")=$A2)*(INDIRECT("'"&C$1&"'!B2: B10")=$B2),0)+1)),$A2&" "&$B2),"")

Copy C2 across & fill down to populate to return a summary matrix of
hyperlinked names. Clicking on the hyperlinks will jump you straight to the
particular source sheet's row where the particular name is located (a useful
feature, no? <g). Just use the back button on the web toolbar to return to
the summary sheet.

Adapt the ranges to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

First, let's stash away the earlier suggestion. Never know when that
might come in handy <g

Ok, it's difficult to visualize what's your current set-up over there
...

Could you use either of the 2 free filehosts listed below to upload
your sample book and then post the link to it in response here (the
link is generated when you upload, just copy and paste it here)

http://www.flypicture.com/
http://cjoint.com/index.php

For cjoint.com (it's in French), just click the "Browse" button,
navigate to folder select the file Open, then click the button
centred in the page below (labelled "Creer le lien Cjoint") and it'll
generate the link. Then copy & paste the generated link as part and
parcel of your response here.

Kindly note that no attachments should be posted *directly* to the
newsgroup.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
On Jan 25, 1:30 am, Charles wrote:
You're right. I could have explained that better. Trying to put into words
what I need, exactly. Relatively new to Excel and trying to wrap my head
around it. Here's the scenario:

I have a workbook of that encompasses many worksheets. There are eight sets
of worksheets that all feed into a central worksheet. What I mean by set are
12 worksheet on a subject, in this case, the run of a theatrical production.
Each worksheet is a single performance. What the worksheets do is allow for
seating reservations and type of ticket sold. The worksheet is already
programmed to tally each type of ticket sold per a legend and centralize the
results on a single worksheet that serves as an overview of the year. Where
my question comes in is that I also have a listing of my season ticket
holders that can come to one performance per show pre-paid and I want a way
to track them by first and last name in a single cell and transfer that info
to the season ticket worksheet, so that we can better track who attends what
performance and prevent multiple "pre-paid showings". Does this make sense?