Match cell to cell on another worksheet to return all relevant rows

**denise6372** · 02-08-2012, 06:38 PM

Hi

I'm looking to take cell A1 on worksheet2 (which happens to be a name), match the name to a column on worksheet1 and return all rows that have data in them into worksheet2.

In case more detail is needed:
Worksheet1 has Names across the top in row 1, with a list of proficiency badges in the column 1 and on individual named summary sheets, I'd like to autopopulate only with badges that they have achieved.

Hoping that makes sense to somebody, I"m not even sure it makes sense to me now.

Thank you

**rylo** · 02-08-2012, 07:45 PM

Hi

1) What is the indicator that a badge has been obtained in sheet1 matrix? a cross, a number what?
2) If you want individual sheets populated, then what is the purpose of the name in A1? or do you only want that person's sheet completed?
3) Are the individual sheets already in the workbook, or do they have to be created?
4) If they exist, are there any exising header details?

Maybe add an example workbook that reflects your situation.

rylo

**denise6372** · 02-08-2012, 11:25 PM

Hi

I don't know how to add an example workbook

I'll try to explain as best I can.

Contact Info worksheet - names are added and with each name added the user has to click command button to input new worksheet; a pop up box requests the name to be entered. This will be the same as the name added to contact worksheet. These worksheets will then become individual summary sheets.
Badge proficiency worksheet - has list of names from contact info worksheet in top row with a list of proficiency badges in first column and an "A" in cells where the badge has been completed.
Individual summary worksheet - named (as per contact info sheet) and name of worksheet is put into cell a1 on each one.

I'm wondering if on the individual summary worksheet I can get a section labelled Badges Obtained, to automatically populate with badges the named person has achieved from the Badge Proficiency worksheeet. Is that clearer? If not please advise how I can add an example thank you.

**denise6372** · 02-09-2012, 12:17 AM

Ah found it I think

Example.xlsm

**rylo** · 02-09-2012, 05:32 PM

Hi

in cell template!A17 array enter (ie hold down the ctrl and shift keys, then press enter) the formula

=IF(ROW()-16>COUNTIF(OFFSET(Proficiency!$A:$A,,MATCH($A$1,Proficiency!$1:$1,0)-1),"A"),"",INDEX(Proficiency!$B:$B,SMALL(IF(OFFSET(Proficiency!$A:$A,,MATCH($A$1,Proficiency!$1:$1,0)-1)="a",ROW($A:$A),""),ROW()-16)))

Copy down to A25. This should bring back the proficiency badges that have been received by the nominated person in A1.

HTH

rylo