Is this possible

[CHerzog]

I have a work sheet with more then seven columns, each column has a cell with
a title in it. I want to know if it is possable to have a column with a
function or formula that will insert the column title if a date is entered in
that column.
I am using Excel 2003. See below for a picture of what I am looking for.

DCN1 DCN2 DCN3

Page 1 DCN1 01/02/06
Page 2 DCN3 01/02/06 05/03/07 08/09/07
Page 3 DCN2 01/02/06 05/03/07

 

[Sheeloo]

Enter this formula in B2 and copy down
=LOOKUP(2,1/C2:Z2,$C$1:$Z$1)
Change Z to the last column in your range

To hide #N/A till a date is entered use
=IF(ISNA(LOOKUP(2,1/C2:Z2,$C$1:$Z$1)),"",LOOKUP(2,1/C2:Z2,$C$1:$Z$1))

 

[CHerzog]

In the lookup Function what do the 2,1 stand for rows or something else?

 

[Sheeloo]

Did it work?
1/C2:Z2 produces an array of the inverse of the values in the range C2:Z2
which is like
{#DIV/0!,1/D2,#DIV/0!,1/F2,#DIV/0!,#DIV/0!,...}

LOOKUP(2,1/C2:Z2,$C$1:$Z$1) tries to find 2, which is not there, so it
returns the position of the last number which is not #DIV/0! and then picks
the corresponding heading from C1:Z1...

Select only lookup in the formula in edit mode and click on Fx to see the
array..

 

[Teethless mama]

Try this:

=IF(COUNT(C2:Z2),LOOKUP(10^10,C2:Z2,$C$1:$Z$1),"")
copy down

 

[CHerzog]

Yes this work thank you.

Did it work?
1/C2:Z2 produces an array of the inverse of the values in the range C2:Z2
which is like
{#DIV/0!,1/D2,#DIV/0!,1/F2,#DIV/0!,#DIV/0!,...}

LOOKUP(2,1/C2:Z2,$C$1:$Z$1) tries to find 2, which is not there, so it
returns the position of the last number which is not #DIV/0! and then picks
the corresponding heading from C1:Z1...

Select only lookup in the formula in edit mode and click on Fx to see the
array..