formula to create a dynamic list of names from a range, based on corresponding range

Try:

=IFERROR(INDIRECT("Data!"&TEXT(AGGREGATE(15,6,(ROW(Data!$F$3:$AN$16)*1000+COLUMN(Data!$F$3:$AN$16))/((Data!$F$3:$AN$16<>"")*(Data!$BF$3:$CN$16="")),ROWS(D$5:D5)),"R0C000"),FALSE),"")

copied down. Sorting is more difficult, becasue of inconsistencies in formatting (Ms, for example, being neither forename nor surname... but just there).

Thank you, worked as I needed except I tried to make the sheet name (Data1, Data2, Data3) dynamically included in the formula, of course I stumbled.

I attached a new worksheet to show you what I was trying to do.

As to the sort, just for learning, what if there was no prefixes, how would I go about sorting the list with a formula.

Again, thank you for your quick prompt, have a wonderful day! (always)

BTW, tried to add to your reputation, the system would not allow me, "You must spread some reputation around before giving it to Glenn Kennedy", the problem is, no one else has ever helped me out

Try the following:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

BTW got this error after "submit reply"
Fatal error: Declaration of vB_DataManager_ThreadRate_Multiple::fetch_query($condition, $limit = 0, $offset = 0) must be compatible with vB_DataManager_Multiple::fetch_query($condition = '', $limit = 0, $offset = 0) in /home/eforum/public_html/includes/class_dm_threadrate.php on line 290

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.