Pulling first and last name from a worksheet to another cell depending on group number.

[israelalvarado]

Help everyone, this some advanced stuff!!!!!!!.


so, i am organizing groups and the group members are always changing around. I have a master sheet with names and group numbers, i have another worksheet where it displays people's first and last name only. for instance, If Joe smith is on group 4. I would love for "joe smith" to be under my group 4 members, but then if I change "joe smith" from group 4 to group 3 to also do that... get me???.


sceneario

under worksheet "master database" under cell C5 has group "5" i want that person's first and last name to be on my "sheet 2" under the header "grupo 5" in any of the numbers listed there.


The "master database" has the groups and "sheet2" is where i have the group sections.

GUYS, PLEASE PLEASE PLEASE, I AM ALL ABOUT LEARNING, SO, IF YOU CAN SOMEHOW EXPLAIN A BIT THE FORMULA, SO THAT I CAN LEARN AND BUG PEOPLE BUT HELP PEOPLE.

THIS IS WAY TOO ADVANCED FOR ME. THANK YOU

[FDibbins]

1st, add a helper column at the end and copy thisx into Q2...
=C2&"-"&COUNTIF($C$2:C2,C2)

What this does is take the group number (C2) and attach the cumulative count for that group number to it. I added a "-" to make sure there is no confusion in the numbers.
So you get...
1-1
3-1
3-2
etc

Then tou pull in the data to each sheet, use this in Group2, copied down and across...
This formula will work for the 1st few columns of data. From C onwards though, those headings dont exist in the database...
=IFERROR(INDEX('Master Database'!$A$2:$P$94,MATCH(RIGHT($A$1,1)&"-"&ROW($A1),'Master Database'!$Q$2:$Q$94,0),MATCH(A$3,'Master Database'!$A$1:$P$1,0)),"")

Broken down, working from inside outwards....
RIGHT($A$1,1)&"-"&ROW($A1)
This takes the right-most character of the heading to get the group number, adds "-" and then adds the row number. I use the row number to increment the value with each row down. This gives the criteria to search for (2-1, 2-2, 2-2 etc)

MATCH(RIGHT($A$1,1)&"-"&ROW($A1),'Master Database'!$Q$2:$Q$94,0)
This searches for the row number, based on the criteria above, in the range shown - and looks for an exact match...the ,0) part

OK so now we have the group and the rownumber, all we need is which column to pull from. Thats where the last MATCH() comes in...1
MATCH(A$3,'Master Database'!$A$1:$P$1,0)),"")
This returns the column number of what it finds in A3, in the range specified.

Hope that helps?

[israelalvarado]

thank you so much,


i tried to understand what you wrote, but I just can't seem to do it nor understand it. I am so soooooorry!.. it's way to advanced for me, do you think you can just copy and paste it?. I simply just don't know how.

[FDibbins]

The formulas you need are in my post above...

Copy this to a helper column in Master database, Put it in Q2 (it should copy itself down, if not, you need to copy it down)
=C2&"-"&COUNTIF($C$2:C2,C2)

Once you have that, you will see how it sets up the groups for you - change a group in C and see how it changes in Q

the next forumla to extract your data to each sheet is...
=IFERROR(INDEX('Master Database'!$A$2:$P$94,MATCH(RIGHT($A$1,1)&"-"&ROW($A1),'Master Database'!$Q$2:$Q$94,0),MATCH(A$3,'Master Database'!$A$1:$P$1,0)),"")

1. Essentially, this boils down to...
=INDEX('Master Database'!$A$2:$P$94,MATCH(2&"-"&1,'Master Database'!$Q$2:$Q$94,0),MATCH(A$3,'Master Database'!$A$1:$P$1,0))
=INDEX('Master Database'!$A$2:$P$94,MATCH(2&"-"&2,'Master Database'!$Q$2:$Q$94,0),MATCH(A$3,'Master Database'!$A$1:$P$1,0))
=INDEX('Master Database'!$A$2:$P$94,MATCH(2&"-"&3,'Master Database'!$Q$2:$Q$94,0),MATCH(A$3,'Master Database'!$A$1:$P$1,0))

2.. The main part here is INDEX()
=INDEX(range,row-num,column-num)
This returns data in Range that is at the intersection of Row-Num and Column-Num
=INDEX(A1:J10,4,6)
This searches in A1:J10 down to the 4th tow and then across to the 6th column and returns the value from that cell (F4)

3. To find the row (and column) number, you use MATCH()
=MATCH(criteria,range,0)
This returns the row number in Range that matches the criteria (the same thing can be used to find the column number)
so if A1:A10 contained the letters A to J...
=match "D",A1:A10,0) would return 4...D is in the 4th row of that range

4. Likewise if A1:J1 contained the letters A to J...
=match "F",A1:A10,0) would return 6...F is in the 6th column of that range.

So, back to the formula...
(I just noticed you uploaded a .xls workbook, .xls (pre-2007) does not support 1 of the functions I used, so I mofified it)
INDEX('Master Database'!$A$2:$P$94, MATCH(RIGHT($A$1,1)&"-"&ROW($A1),'Master Database'!$Q$2:$Q$94,0), MATCH(A$3,'Master Database'!$A$1:$P$1,0))

from 3. above, we need to ID the group/sheet, so I pulled that from the heading RIGHT($A$1,1)
I then included "-" and finally used ROW() for the count increment (see 1.)

Finally I wrapped the who;e thing in IF(ISERROR() to error trap

See if this makes any more sense to you, and take alook at the attached file

[FDibbins]

Thanks for the feedback