Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup based on 1st non-blank cell
This should be easy but I'm having problems. Data looks like this.
Jan Feb Mar Apr.........Dec John 10 5 6 Mary 2 4 Jill 5 1 5 I want to add a column that will find the first non-blank cell after the name and then return the column header to that cell so it looks somethng like this. Name Start John Feb Mary Mar Jill Jan And if there is never a non-blank cell I'd like it to return N/A. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup based on 1st non-blank cell
Try this:
=INDEX(B$1:M$1,MATCH(TRUE,INDEX(B2:M2<"",1,),0)) Copy down as needed. -- Biff Microsoft Excel MVP "Rominall" wrote in message ... This should be easy but I'm having problems. Data looks like this. Jan Feb Mar Apr.........Dec John 10 5 6 Mary 2 4 Jill 5 1 5 I want to add a column that will find the first non-blank cell after the name and then return the column header to that cell so it looks somethng like this. Name Start John Feb Mary Mar Jill Jan And if there is never a non-blank cell I'd like it to return N/A. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup based on 1st non-blank cell
=IF(COUNTA($B2:$M2)=0,"N/A",INDEX($B$1:$M$1,MATCH(TRUE,$B2:$M2<"",0)))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) or if you can live with #N/A: =INDEX($B$1:$M$1,MATCH(TRUE,$B2:$M2<"",0)) Rominall wrote: This should be easy but I'm having problems. Data looks like this. Jan Feb Mar Apr.........Dec John 10 5 6 Mary 2 4 Jill 5 1 5 I want to add a column that will find the first non-blank cell after the name and then return the column header to that cell so it looks somethng like this. Name Start John Feb Mary Mar Jill Jan And if there is never a non-blank cell I'd like it to return N/A. -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup based on 1st non-blank cell
Assuming
Months on cells B1:M1 Name on cells A2:A4 Create defined names for all the names eg. John is a defined name range from B2:M2 Mary is a defined name range from B3:M3 and so on... If your criteria names eg. Mary in cell A9 John in cell A10 and so on... In cell B9: =INDEX($B$1:$M$1,MATCH(TRUE,INDIRECT(A9)<"",0)) ctrl+shift+enter, not just enter copy down "Rominall" wrote: This should be easy but I'm having problems. Data looks like this. Jan Feb Mar Apr.........Dec John 10 5 6 Mary 2 4 Jill 5 1 5 I want to add a column that will find the first non-blank cell after the name and then return the column header to that cell so it looks somethng like this. Name Start John Feb Mary Mar Jill Jan And if there is never a non-blank cell I'd like it to return N/A. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count with multiple criteria based on cell not blank? | Excel Worksheet Functions | |||
Function to give value of a BLANK cell based on another | Excel Worksheet Functions | |||
Returning a blank cell rather then #N/A (Lookup) | Excel Discussion (Misc queries) | |||
Return of blank cell if lookup fails | Excel Worksheet Functions | |||
Lookup with search range start based on position of last blank lin | Excel Worksheet Functions |