Refining a Range of Cells

[Ark68]

I have a dynamic range of 12 cells, A5-A16, that could contain any text value, included the default "unavailable". I would like to recreate that list at B5, but exclude all the "unavailable" values, leaving just the relevant contents (for use in a validation list)

 

[Akashwani]

Hi,

Does this help?...

Excel Workbook
	A	B
4	List	New List
5	Cat	Cat
6	Dog	Dog
7	Unavailable	Rat
8	Rat	Bull
9	Bull	Red
10	Red	White
11	White	Green
12	Green	Blue
13	Blue	Black
14	Black	Cow
15	Cow	*
Sheet1

The formula in B5 needs entering with ctrl shift enter NOT enter.
You can then copy the formula down.

Ak