Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
search and copy text from one sheet to others
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
search and copy text from one sheet to others
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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "simpsonehh" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
search and copy text from one sheet to others
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? "Max" wrote: 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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "simpsonehh" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
search and copy text from one sheet to others
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" wrote: 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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "simpsonehh" wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
search and copy text from one sheet to others
"simpsonehh" wrote:
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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search for rows in one sheet and copy into another sheet based on customer id | Excel Worksheet Functions | |||
How to search column, copy row, and copy to another sheet in same | Excel Discussion (Misc queries) | |||
Search and copy certain text | Excel Discussion (Misc queries) | |||
How to search and copy cell content to another sheet? | Excel Worksheet Functions | |||
SEARCH COPY OF TEXT | Excel Discussion (Misc queries) |