Capturing matched data from one tab to another

**FiBell** · 09-24-2012, 06:10 PM

Dear Forum

I am trying to set up my file so that when a column in one tab contains a particular character string,
then a prescribed set of cells from that associated row are automatically copied to a row in another tab.

I have attached an example file where there is a list of teacher schools and names in the Teacher Info tab.
Tabs C1 and JM2 are event tabs with data from each delegate pertinent to the event.

If a course code of C1 is entered on the teacher's row in any of the 3 colums for Course code1,2 or 3,
I would like the tab C1 column to have the list of schools and names for the people who are listed to go on that course
In my example that would be the data for people 1,2,4,&5.

I would be grateful for any help you can suggest, as I have gone round the 'houses' several times without success.
My experience of this Forum is that there has always been some amazing person on the other side of the world who can work it out in minutes!

Fingers crossed!

**JBeaucaire** · 09-24-2012, 08:00 PM

This is a completely formula-based solution. There is a set of KEY formulas on the Teacher Info sheet in columns K:O. You can add more if needed for other possible course codes. Just add the code at the top of the column, then copy the formulas over, too.

I've setup your first C1 sheet. I removed the word "event" from the tab name so the sheet name and the course codes matched exactly.

Then on the C1 sheet the rest is automatic.

B1 lists the course code by reading the sheet name.
C1 lists how many entries should appear in the list
A3 is the first formula, copied A3:C25 to get you started.

===============
Adding a new sheet is easy. Right-click the C1 tab and select Move or Copy, make a COPY of that sheet, then rename it JM2....Done! The formulas do the rest.

**FiBell** · 09-25-2012, 02:59 AM

Thank you SO much! I and my Teaching School organisation are VERY grateful. Am going off to integrate this into my application - it looks perfect and comprehensively understandable.
My eyes were drawn to the Ray Kroc quote at the bottom of my thread "None of us is as good as all of us" - which was true of my recent volunteering at the London 2012 Olympics and is most definitely true of this Forum. I just wish I was good enough to design formula solutions like this to share with others.

**JBeaucaire** · 09-25-2012, 07:35 AM

Glad to help. I have marked this thread solved for you.
In the future please select Thread Tools from the menu above and mark the thread as solved. Thanks.