Create Dynamic Text List From Column

**VDO** · 02-19-2011, 09:56 PM

Hi Folks,

I have a Excel 07 sheet I'm working on featuring a column which populates with Customer's names, if various criteria are met.

So what I have is a column with some names in it and a lot of blanks.

I'd like to be able to capture the names and create a list of them on my sheet's 'Summary' tab, preferably using a formula. Obviously counting the number of names that appear is no problem, but it would be pretty cool to have the individual names listed alongside.

Any suggestions greatly appreciated!

Dave

**todd1016** · 02-19-2011, 11:02 PM

Dave,
There is probably an easier way but this will accomplish it. This will require 1 helper column that counts the names. This assumes your names are in Column E and this is coppied to Column F. I use the row command -1 in the indirect to find the range of all rows in Column E above this row. This is Column E : ROW() - 1. I use this range in a Max and then increment that value by 1. The If statement will skip over the rows in the Name column that are blank.

=IF(E6="","",MAX(F1:INDIRECT("F"&ROW()-1))+1)

Next one is a little trickier, use the row that the formula is in to act as the index. Here I subtract 5 because the first row is row 6, 6-5 is 1 so I know I want the first name. I use the match command to find the row that the first name is in and the indirect to pull that name from Column E in that row.

=IFERROR(INDIRECT("e"&MATCH(ROW()-5,F:F,0)),"")

This last command can be copied to a column in your Summary sheet with the sheet reference in the formula.

Hope this helps.
Todd

**VDO** · 02-20-2011, 05:12 PM

That does the trick Todd, many thanks for taking the time to reply.

In case anyone reads this in future and wants options another solution has been devised by Tom Ogilvy:

In the tab you want to put the list of names use the following array formula in row 8 and copy it down to as many cells as you need:

{=IF((ROW()-7)<=COUNTIF(Customers!$H$5:$H$20,">"""""),INDEX(Customers!H:H,SMALL(IF(Customers!$H$5:$H$20<>"",ROW($H$5:$H$20)),ROW(A1)),1),"")}

where 'Customers' tab H5:H20 is the location of the column of names.

Dave