| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this possible
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is this possible
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" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is this possible
In the lookup Function what do the 2,1 stand for rows or something else?
"Sheeloo" wrote: 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" wrote: 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is this possible
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.. "CHerzog" wrote: In the lookup Function what do the 2,1 stand for rows or something else? "Sheeloo" wrote: 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" wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is this possible
Try this:
=IF(COUNT(C2:Z2),LOOKUP(10^10,C2:Z2,$C$1:$Z$1),"") copy down "CHerzog" wrote: 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Is this possible
Yes this work thank you.
"Sheeloo" wrote: 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.. "CHerzog" wrote: In the lookup Function what do the 2,1 stand for rows or something else? "Sheeloo" wrote: 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" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
