The formula I posted needed one more tweak....

Try this regular formula:
=IF(COUNTA($A$17:$A$26)=ROWS($43:43),INDEX($A$17: $A$26,SMALL(INDEX((ISBLANK($A$17:$A$26)*10^99+ROW( $A$17:$A$26)-ROW($A$17)+1),0),ROWS($43:43))),"")

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Ron Coderre" wrote in message
...

Try something like this:

A43:
=IF(COUNTA($A$17:$A$26)=ROWS($43:43),INDEX($A$17: $A$26,SMALL((ISBLANK($A$17:$A$26)*10^99+ROW($A$17: $A$26)-ROW($A$17)+1),ROWS($43:43))),"")

Copy that formula down through A52

Note: in case text wrap impacts the display, there are NO spaces in that
formula.

Using your sample data, that formula returns these values in A43:A52
PY02
PY04
PY05
PY06
PY07
PY08
PY09
PY10
PY11
(blank)

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"shorticake" wrote in message
...
I have the following text in column A rows 17 through 26

17 PY02
18
19 PY04
20 PY05
21 PY06
22 PY07
23 PY08
24 PY09
25 PY10
26 PY11

Then I use this formula
=IF(ROWS($1:1)<=COUNTA(A17:$A$26),INDEX($A$26,SMAL L(IF($A$26<"",ROW($A$26)-MIN(ROW($A$26))+1),ROWS($1:1))),"")
in A43 and copy down to A52.

I'm expecting it to return the following in Column A rows 43:52

43 PY02
44 PY04
45 PY05
46 PY06
47 PY07
48 PY08
49 PY09
50 PY10
51 PY11
52

But I'm getting this instead:

43 PY02
44 PY05
45 PY06
46 PY08
47 PY10
48
49
50
51
52

I don't know why it's skipping PY04, PY07, PY09, and PY11.

Any help is greatly appreciated!