Lookup values from an array on one tab and transferring it to another tab in a specific order

[sanimushin]

Giving an example because its very hard to explain.

IE:
I have formulas that searches for employee names on sheet 1, A2:A15. All ten rows are locked with those formulas and formatted as "text". Some rows are blank and some have names in it.

I am on sheet 2, and I want to transfer those the values in (sheet 1, A2:A15) to sheet2, B2:B15. A2, A5, and A15 have names in it, while A3:A4, A6:A14 have formulas without any names in it.

How can I transfer those names in sheet 1 A2, A5, A15 in that specific order to sheet 2, B2, B3, B4.
If the orders of the names in A2:A15 changes to different cells A3, A4, A5, A8 instead of A2, A3, A15. Names transferred to sheet 2 should be in the order of B2, B3, B4, B5.

Not sure if this makes any sense at all. Please advise. Thanks!

 

[Aladin Akyurek]

In B1 of Sheet2 enter:

=COUNTIFS(Sheet1!A2:A5,"?*")

In B2 control+shift+enter, not just enter, and copy down:

=IF(ROWS($B$2:B2)>$B$1,"",INDEX(Sheet1!$A$2:$A$15,SMALL(IF(1-(Sheet1!$A$2:$A$15=""),ROW($A$2:$A$15)-ROW($A$2)+1),ROWS($B$2:B2))))

 

[Aladin Akyurek]

Control+shift+enter: Press down the control and the shift keys at the same time, while you hit the enter key. When done properly, Excel itself puts a pair of { and } around the formula in recognition.

 

[sanimushin]

It says Array formula is not valid in merged cells, but i do not have any merged cells.

 

[Aladin Akyurek]

It says Array formula is not valid in merged cells, but i do not have any merged cells.

See the workbook which implements the suggested set up: https://dl.dropboxusercontent.com/u/65698317/sanimushin sublist.xlsx

 

[sanimushin]

It is working now. Thank you so much

 

[Aladin Akyurek]

It is working now. Thank you so much

You are welcome.