Get names from different cells

[Sheepkin_Coat]

I have a sheet with people names in cell b and in cell G, N, U, AT and BA i have other details and i want to list the people that have entries in only the above cells

I have included a example example1.xlsx

[Pete_UK]

Put this formula in A1 of Sheet1:

=IF(B1="","",IF(COUNTA(G1,N1,U1,AT1,BA1)>0,COUNTIF(B$1:B1,"?*"),""))

then copy down to the bottom of your data (A9) or beyond. Then put this formula in A1 of Sheet2:

=IFERROR(INDEX(Sheet1!B:B,MATCH(ROWS($1:1),Sheet1!A:A,0)),"")

and copy down as far as required.

Hope this helps.

Pete

[Pete_UK]

Actually, it would be better to insert a blank row at the top of Sheet1 so all the data moves down (you can use this for headings if you like), and then you can use this formula in A2:

=IF(B2="","",IF(COUNTA(G2,N2,U2,AT2,BA2)>0,MAX(A$1:A1)+1,""))

or this one:

=IF(B2="","",IF(COUNTA(G2,N2,U2,AT2,BA2)>0,COUNTIF(A$1:A1,">0")+1,""))

Copy down to the bottom of your data.

The other formula remains the same.

Hope this helps.

Pete

[Sheepkin_Coat]

Thanks Again, that great, just one more thing.

what i really want is to only get the names that have something in G,N,U,AT & BA

When i added another name to the list it still print it on the second sheet also i would like it to print like this

Row A
Name

Row B
contents of G

Row C
contents of N

Row D
contents of U

Row E
contents of AT

Row F
contents of BA

[Pete_UK]

I'm not really sure what you are looking for - can you attach another mock-up showing manually what you expect to get in the second sheet.

Pete