Formula that selects value from list in sequence order
 

I have a series of text data entries in P5:P149.
I also have data in some of the cells in Column I.

In Column K, I want to select the data from Column P in sequential order, IF
there is data in Column I. If there is not data in Column I, I want it to
advance to the next time data appears in Column I, without skipping the
selection order of Column P.

Hope that makes sense? Thanks!

Formula that selects value from list in sequence order
 

Try this...

K4 = formula to count how many records meet the condition:

=COUNTA(I5:I149)

Enter this array formula** in K5 and copy down until you get blanks:

=IF(ROWS(K$5:K5)<=K$4,INDEX(P$5:P$149,SMALL(IF(I$5 :I$149<"",ROW(P$5:P$149)),ROWS(K$5:K5))-ROW(P$5)+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Scott" wrote in message
...
I have a series of text data entries in P5:P149.
I also have data in some of the cells in Column I.

In Column K, I want to select the data from Column P in sequential order,
IF
there is data in Column I. If there is not data in Column I, I want it to
advance to the next time data appears in Column I, without skipping the
selection order of Column P.

Hope that makes sense? Thanks!

Formula that selects value from list in sequence order
 

If I understand your question, enter this in K5 and copy down as needed:

=IF(I5="","",INDEX(P$5:P$145,COUNTA($I$5:I5)))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Scott" wrote in message
...
I have a series of text data entries in P5:P149.
I also have data in some of the cells in Column I.

In Column K, I want to select the data from Column P in sequential order,
IF
there is data in Column I. If there is not data in Column I, I want it to
advance to the next time data appears in Column I, without skipping the
selection order of Column P.

Hope that makes sense? Thanks!