Is this possible

C

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
 
S

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))
 
S

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..
 
C

CHerzog

Yes this work thank you.

Sheeloo said:
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..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top