I have three worksheets containing weekly project schedule details such as the following: Col A-----Col B------Col C------Col D Project---07/02/07---07/09/07---07/16/07 ___________________________________________ prj 1-----*blank*----CU study---testing prj 2-----CU study---meeting----*blank* Each worksheet has one distinct schedule but has the same column headings. What I want to do is the combine the three worksheets (slave) into one 'master' schedule, and have the master reflect any changes made to the slaves. In addition, I want the master to show only the cells with the specific text "CU study" and not anything other details. For example, a cell in the slave containing only the words "brainstorming meeting" will not show up in the master, and a cell containing the words "CU Study in lab 123" will show up exactly as that in the master. I have tried using pivot tables but it limits to numerical values only. I want to show the strings of text. I have no experience writing macros but I would be able to learn it if anybody could help. Thank you!

Unfortunately, you can't use wildcards in an IF statement. If you could, you could just populate your Master sheet with IF's such as =IF(Sheet2!A1="*CU Study*",Sheet2!A1,"") However, VBA has a LIKE operator that does allow the use of wildcards, so you can create a UDF (User Defined Function) that can be used in a spreadsheet. Open the VBA editor and insert a module. Paste in this code: Function CuLike(Text As String, Filter As String) As Boolean CuLike = Text Like Filter End Function Now, back in your Master sheet you can use: =IF(CuLike(Sheet2!A1,"*CU Study*"),Sheet2!A1,"") Since the VBA Like operator can use the wildcards, CuLike() will be true for any cell that contains the text "CU Study" anywhere in the cell. If CuLike is true, then the IF stament will return the entire contents of that cell. Hope that helps!

Thank you for your help! I tried it and it worked great. Do you have any suggestions about how I make the string of text case-insensitive so that "cu study", "CU Study" and "CU study" will all show up in the master if entered in the slave worksheet.

I believe this will work: Function CuLike(Text As String, Filter As String) As Boolean CuLike = UCase(Text) Like UCase(Filter) End Function Within VBA, UCase forces the contents of the variables Text and Filter to be all uppercase, without actually changing the contents of either variable. That way, whatever is the uppercase version of the text in the cell is compared to the uppercase version of what you are searching for. That makes the Search/Find case-insensitive.

Thanks again for your help! May I know how could I go about merging this information from the same array (say, A1:D10) from three different worksheets into one master worksheet. In addition, the master also needs to have the ability to track and record changes in any of the slave sheets. That is, if cell A1 in sheet 1 has "cu study 1", cell A1 in sheet 2 has "cu study 2", the master sheet should have A1="cu study 1", A2="cu study 2". In addition, if a row is added in in sheet 1 so that cell A2 now has "cu study 3", the master sheet will also add a row between the existing A1 and A2 so that now A1="cu study 1", A2="cu study 3" and A3 = "cu study 2". The column headings do not change but rows could be added. Visually, it is like: Sheet 1: ColA-------ColB--------ColC Date-------07/02-------07/16 proj1------cu study 1------ Sheet 2: ColA-------ColB--------ColC Date-------07/02-------07/16 proj2------------------cu study 2 Master sheet ahould show: ColA-------ColB--------ColC Date-------07/02-------07/16 proj1------cu study 1------ proj 2-----------------cu study 2 In addition, when new rows are added or changes are made to existing rows in one of the slave sheets, for example: Sheet 1: ColA-------ColB--------ColC Date-------07/02-------07/16 proj1------------------cu study 1 *change made* proj3------cu study 3------- *new row* The master sheet will insert a row and group all the rows from the same sheet so that: Master sheet: ColA-------ColB--------ColC Date-------07/02-------07/16 proj1------------------cu study 1 proj3------cu study 3-------- proj2------cu study 2-------- I would really appreciate any help on this! Thank you!

It's now time for me to ask - What are your overall requirements? I provided a solution for finding and copying specific text (your first request) and then I provided a solution for finding and copying case insensitive specific text (your second request). Your third request is something very different. Adding rows in the master sheet when rows are added to the slave sheets and then finding, copying and *grouping* case insensitive specific text is a very different set of requirements. It may mean that the time spent solving the first 2 requests was wasted. For example, it appears that the latest solution also has to copy the project number from the slave sheet to the master, possibly leading us to use VBA to copy the entire row and then eliminate the unwanted data. You see how that is totally different from where we started? Before I spend any time coming up with a solution for your third request, could you please let me know if there is going to be anything else that I need to consider? Let's start with these items: 1 - You say want to merge "this information from the same array (say, A1:D10) from three different worksheets” but then you say rows might be added in the slave sheets. Does a new row expand the array to A1:D11 or do you still only want A1:D10? In other words, do you really want a specific array from each sheet or do you want every row that contains "CU Study" in all sheets? Each of these situations is handled differently. 2 - In your example, there is no distinction between the data that came from Sheet 1 and the data that came from Sheet 2. You note that you want the data from each sheet grouped together - do you also need to know where each group came from? 3 - Will you also be adding columns to the arrays in the slave sheets as the dates go out further? 4 – Is there a set number of slave sheets or could it be any number at any time? Try to list all the requirements that you think I will need to deal with. While there is some generic flexibility that can be built into a solution, as you can see, certain requirements may mean dismantling what we've already built and starting over. I really don’t want to do that (again).

Hello DerbyDad03, I apologise for not stating all the overall requirements at the first time as I was trying to learn the basics and work your solutions step-by-step into my overall project. I have no experience in VBA and was trying to incorporate your first two solutions using basic Excel formulae when I ran into the higher-level problems I stated above. I will be as comprehensive as I can. 1. I have four slave sheets (S1 to S4), which I want to use to build a master schedule from. The slave sheets have extra information which I do not want reflected in the master. The number of slave sheets is fixed. The slaves are all in the following unified format: ColA-----ColB------ColC------ColD Date------07/02-----07/09-----07/16 Current(*this is a heading) SlProj1---cu study 1----------meeting S1Proj2---lab-------cu study 2------- Pending (*this is a heading) S1Proj3-------------cu study 3------- Completed (*this is a heading) S1Proj4---meeting---cu study 4------- The row headings are the Monday of each week. I used the first date (07/02) as a tack and created the subsequent row headings by adding 7 to the cell on the left: C1=B1+7, D1=C1+7...and so on. I ended the dates after about 50 columns by not entering any more formulae into the cells but the dates are meant to be a running record and the columns should extend infinitely. Similarly, the number of rows (projects) are not fixed and are meant to accomodate any additions of projects. Also, the contents of each project row are not fixed (could have additions, deletions, shifting to the left or right etc.). 2. The master looks at the entire slave sheet (infinite columns and rows) and merges the four slaves (S1 to S4) together. The overall purpose of the master is to display what "cu studies" are happening for what project in what sheet in which week. Specifically, the master needs to do the following for merging: - Look at all the entire slave sheet as far as the rows and columns go. There is no fixed array. - Pick out only cells in the slaves with the case-insensitive text "cu study" and copy the entire content of that cell into the master. - Look horizontally to the left at the corresponding project name in column A and copy that project name as well. - Look vertically up to see the date it falls under and place the copied cell in the correct date column. - Group all the rows from slave S1 under the sheet name 'S1', all rows from slave S2 under the sheet name 'S2' etc. Therefore, there should be four additional rows containing the sheet names. -Within each sheet heading, place the projects under the same row heading as in the slave, "Current", "Pending" or "Completed". Therefore, there should be 12 additional rows (two for each sheet) containing the headings "Current", "Pending", "Completed". - Monitor and record all changes made to current, pending and completed projects in S1 to S4: ---e.g. If there a project row containing a "cu study" is added in sheet S1 under the heading "Current", insert a blank row in the master under the sheet name "S1" and under the heading "Current" and copy the row from slave to master. The copied row in the master should include only cells with the text "cu study" and correspond to the correct date columns. Cells with other text such as "meeting" etc. should not be shown in the master. ---If a row is deleted in any of the slaves, the corresponding row is also deleted in the master and the remaining rows will shift up so that there are not blank rows in the master. --If a "cu study" is postponed in the slave, say from 07/02 to 07/16, the same study will also shift two cells to the right from 07/02 to 07/16 in the master. Other requirements are: -That changes are made only to the four slaves. No direct changes should be made to the master. -The master refreshes as and when any changes are made to the slaves so that the most updated information is always shown in the master. Again, thank you for your time and help and please let me know if I need to clarify anything.