details of month

[srinivasan]

I am having a work sheet with each column representing a month from January
to December. i.e from C column to N column) The list of branch offices are
indicated in column B.The details of sales from various branch offices are
fed against each month in the corresponding row. Some offices report the
details promptly while many report belatedly. My requirement is that at a
glance I want to know the latest month for which the details are recd from
branch office to be indicated in the last column next to December by a
formula( month name not figures). Is this possible? Thanks for the
respondents in advance.

 

[Bob Phillips]

For row 2

=INDEX($C2:$N2,1,MONTH(TODAY()))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[srinivasan]

For row 2

=INDEX($C2:$N2,1,MONTH(TODAY()))

--

HTH

RP
(remove nothere from the email address if mailing direct)





No. this does not work.The formula should see if there is any figures in a column and if so it should indicate the month noted against the same in the top row

 

[Bob Phillips]

Okay, so adjust it

=INDEX($C2:$N2,1,MONTH(TODAY()))

--

HTH

RP
(remove nothere from the email address if mailing direct)

=INDEX($1:$1,1,MAX(($C2:$N2<>"")*(COLUMN($C2:$N2))))

which is an array formula so commit with Ctrl-Shift-Enter


a column and if so it should indicate the month noted against the same in
the top row

 

[Govind]

Hi,

IF your first month data for the first branch is in cell C3, then enter
this formula for the first branch after the December month column and
copy it down to all branch rows

=OFFSET(C3,0,COUNT($C$3:$N$3)-1)

Regards

Govind.

 

[Govind]

Hi,

A small correction. Assuming that the months are listed from C3 to N3
and monthwise data from branches are listed in cells C4 to N4, paste
this formula for the first branch and copy it down


=OFFSET($C$3,0,COUNT(C4:N4)-1)

Govind.

 

[srinivasan]

Hi,

A small correction. Assuming that the months are listed from C3 to N3
and monthwise data from branches are listed in cells C4 to N4, paste
this formula for the first branch and copy it down


=OFFSET($C$3,0,COUNT(C4:N4)-1)

Govind.

Thanks a lot Mr Govind and Mr Bob, Both the formulas are fine working. I
was searching for the same for a long time. My regards.
srinivasan

 

[Krishnakumar]

Hi Srinivasan,

Try,

=INDEX($C$1:$N$1,MATCH(1E+307,C2:N2))

where C1:N1 houses months

Govind's formula will fail if there is a blank cell among the dat
range

 

[srinivasan]

Hi Srinivasan,

Try,

=INDEX($C$1:$N$1,MATCH(1E+307,C2:N2))

where C1:N1 houses months

Govind's formula will fail if there is a blank cell among the data
range.

Thanks Mr Krishnakumar, your formula is working fine and shows error message
whenever all the columns are emplty and is useful. But I want to know the
logic behind 1+307 you used next to match function

srinivasan

 

[Krishnakumar]

Hi Srinivasan,

But I want to know the logic behind 1+307 you used next to matc
function

This value is the highest value that can be represented in Excel.

Infact the value is 9.999999999999999E+307

HT