Date calculation function - help

[iwtci]

Hi,

i have a problem that have twisted my brain without any positive
solution. i appreciate your help and assistance.

For better understanding, i enclosed a sample of my workbook.

I need a function (call it MyFunction) to give me The month name /
column header (picked up from corresponding cells in row 11) of the
first cell in each row (from row 12 to row 32) having a numeric entry,
then in cell g12 to have this function =date($e$3,MyFunction,1)

For example, this desired function must return April 1st, 2004 in cell
G12 & September 1st, 2004 in g32 and so on for the other cells in
g12:g32.

Is this possible!?

Appreciate your assistance!?

Khaldoun Abu Zeid

Attachment filename: this goes to list.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=555885

 

[Bernie Deitrick]

Khaldoun,

I don't open attachments, but you could try an array formula (entered with
Ctrl-Shift-Enter) like this in cell G12:

=DATEVALUE(INDEX($A$11:$F$11,1,MIN(IF(ISNUMBER(A12:F12),COLUMN(A12:F12),""))
) & " 1, " & $E$3)

Format as a date in the style you want.

HTH,
Bernie
MS Excel MVP

 

[Ron Rosenfeld]

Hi,

i have a problem that have twisted my brain without any positive
solution. i appreciate your help and assistance.

For better understanding, i enclosed a sample of my workbook.

I need a function (call it MyFunction) to give me The month name /
column header (picked up from corresponding cells in row 11) of the
first cell in each row (from row 12 to row 32) having a numeric entry,
then in cell g12 to have this function =date($e$3,MyFunction,1)

For example, this desired function must return April 1st, 2004 in cell
G12 & September 1st, 2004 in g32 and so on for the other cells in
g12:g32.

Is this possible!?

Appreciate your assistance!?

Khaldoun Abu Zeid

Attachment filename: this goes to list.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=555885

In G12 put the array-entered formula:

=INDEX($K$11:$V$11,1,MATCH(TRUE,ISNUMBER(K12:V12),0))

To array-enter a formula, after typing or pasting it in, hold down
<ctrl><shift> while hitting <enter>. XL will place braces {...} around the
formula.

You can copy/drag it down to G32, but this may mess up your other formatting
and that will need to be corrected.

The formula will give #N/A if there has been no entry. If that's not what you
want, an array formula such as:

=IF(COUNT(K12:V12)=0,"not
installed",INDEX($K$11:$V$11,1,MATCH(TRUE,ISNUMBER(K12:V12),0)))

could be modified to what you want.




--ron