Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looking for formula that selects last value entered in a column? | Excel Worksheet Functions | |||
2 part macro question (sequence & order) | Excel Worksheet Functions | |||
Is there an formula to reverse the order of a number sequence? | Excel Discussion (Misc queries) | |||
Start Macro after user selects a choice from a pick list | Excel Discussion (Misc queries) | |||
How do I reverse the order (sequence) of a column of data in Excel | Excel Worksheet Functions |