search and copy text from one sheet to others

[simpsonehh]

I would like to copy cells from one worksheet to other worksheets where a
portion of the value matches.
I have one file with several worksheet tabs.
Example: Dept 1 - Members, Dept 2 - Members...Dept 30 - Members

Sheets 2, 3 ,4 must not be included in formula (I can move them to new file
if needed)

All the sheets only have Data in column A and data starts on row 1

Dept 1 - Members column A looks like this: (abc-username)
abc-smithb
abc-yeltzb
acb-zooa

all the other sheets look like this (username)
smithb
yeltzb
zooa

I need to take the user in column A of Dept 1 - Members sheet and populate
column B where ever his
username appears in all the other sheets (it could be on multiple sheets).

result should look like
Dept 12 - Members sheet

column A Column B

smithb abc-smithb
yeltzb abc-yeltzb
zooa acb-zooa

Dept 14 - Members
smithb abc-smithb
yeltzb abc-yeltzb
zooa acb-zooa

The sheet "Dept 1 - Members" should remove the abc-username from that sheet
only and data be consolidated to
eliminate empty rows. The abc-username should only be removed if a match is
found on another sheet.

Also, I would need to know how to enter the function/script in to excel 2003.

 

[Max]

Source list assumed in sheet: Dept 1, within A1:A100, viz:

abc-smithb
abc-yeltzb

etc

In the other dept sheets, eg in: Dept 12,
Paste this into B1's formula bar, then array-enter it,
ie press CTRL+SHIFT+ENTER to confirm the formula:
=IF(A1="","",INDEX('Dept 1'!A$1:A$100,MATCH(TRUE,ISNUMBER(SEARCH(A1,'Dept
1'!A$1:A$100)),0)))
Then just copy B1 down as far as required to return the required results

 

[simpsonehh]

Hi,
thank you for the response. That formula looks like it is working great.
May I ask one more favor. How would I get it to clear the cell from the
source sheet if a match is found?

 

[simpsonehh]

Hi,
Rather than remove the entry from the source list. I want to change the
font red or some other color. Is that possible?

Thanks for the help.

 

[Max]

thank you for the response. That formula looks like it is working great.

Welcome. Please press the "Yes" button from where you read this.

How would I get it to clear the cell from the source sheet if a match is found?
Rather than remove the entry from the source list. I want to change the
font red or some other color. Is that possible?

Formulas cannot do the above. Try a new post in .programming.