Name Matrix

[Liz J]

I would like to create a report that takes a list of people's names with
their tasks and place this information into a matrix. Let me explain with
the following information:

In sheet 1, I have a table that has the following columns: Report Name,
Report Description, Dept Published By, Distribution Names
NOTE: The "distribution names" column data are written like this: Mark
Smith, Joe Johnson, David Jones, (etc)

In sheet 2, I have all a table that has the "Distribution Names" for the
y-axis and the "Report Name" in the x-axis.

Is there any way I can make a formula that would allow the data from sheet 1
populate into sheet 2?

Thanks!

 

[Sheeloo]

Hello Liz,

Yes, it can be done...
Pl. confirm that in Sheet2 (where you want the matrix) Report Names are in
Col A (going down vertically in A2,A3,...) and People Names are in
Horizontal... B1, B2,.. and so on.
What you want is put an X at the cross section of Report and People name if
the name is there in Col D of Sheet1...
If yes then
Assumption - Row 1 in both sheets has headers.
Names are in Col D of Sheet1 and max 10 names...

then do this
1. On Sheet1
Copy Col D to Col E
Select Col E
Choose Data|Text to Columns and click FINISH
this will get all the names separate in Columns E to N
2. On Sheet2
enter the names in A2:K2 in the order you want...
enter this in B2
=IF(ISNA(MATCH(B$1,Sheet1!$D2:$M2,0)),"","X") and copy right till K2
Then copy B2:K2 down to the end of your data set...

and you are done

 

[Herbert Seidenberg]

Or use Excel 2007 Tables
with structured references:
http://www.savefile.com/files/1851857