Find First populated cell in row - grab column heading

[jdoughe3]

Scenario -

Column headings are dates

Within a row heading, I want to find the first populated cell in my ro
- and return the column heading above that cell (the date)

Make sense?

Appreciate any assistance you can offer.

Jame

 

[Frank Kabel]

Hi
one way: Try the following array formula (entered with
CTRL+SHIFT+ENTER)
=INDEX(1:1,1,MIN(IF(2:2<>"",COLUMN(2:2))))

Assumptions:
- first row contains your dates
- second row contains the values

 

[Peo Sjoblom]

Another way also array entered

=INDEX(1:1,MATCH(TRUE,2:2<>"",0))

--


No private emails please, for everyone's
benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[jdoughe3]

Thanks Frank - I can't seem to get it to work - but I have to admit tha
I'm a bit out of my league here...

I've attached the file - appreciate any additional help

Attachment filename: excel example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=57228

 

[jdoughe3]

Can't get it to work.... Here's a depiction of my scenario (also
available in excel file attached)
Start Date || End Date || 1/19 || 1/26 || 2/2 || 2/9
<formula> ||<formula>|| || || 40 || 40

I want start date to return 2/2 and end date to return 2/9..


Thanks for your continued efforts!

 

[Aladin Akyurek]

Start Date:

=INDEX(1:1,1,MIN(IF(ISNUMBER(1:1),COLUMN(1:1))))

which must be confirmed with control+shift+enter instead of just enter.

End Date:

=LOOKUP(9.99999999999999E+307,1:1)

 

[Frank Kabel]

Hi Aladin
not sure about the data structure of the OP's file but if the data is
in row two and A2 and B2 are used to stored these values´(and row one
is a heading row with all dates) then your formulas would return a
circular reference. For the OP in this case just change them slightly
to:
=INDEX(1:1,1,MIN(IF(ISNUMBER(C2:IV2),COLUMN(C2:IV2))))

and
=INDEX(1:1,1,MAX(IF(ISNUMBER(C2:IV2),COLUMN(C2:IV2))))

both array entered