Home |
Search |
Today's Posts |
#1
|
|||
|
|||
details of month
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. |
#2
|
|||
|
|||
For row 2
=INDEX($C2:$N2,1,MONTH(TODAY())) -- HTH RP (remove nothere from the email address if mailing direct) "srinivasan" wrote in message ... 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. |
#3
|
|||
|
|||
"Bob Phillips" wrote: For row 2 =INDEX($C2:$N2,1,MONTH(TODAY())) -- HTH RP (remove nothere from the email address if mailing direct) "srinivasan" wrote in message ... 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. 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 |
#4
|
|||
|
|||
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 "srinivasan" wrote in message ... "Bob Phillips" wrote: For row 2 =INDEX($C2:$N2,1,MONTH(TODAY())) -- HTH RP (remove nothere from the email address if mailing direct) "srinivasan" wrote in message ... 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. 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 |
#5
|
|||
|
|||
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. srinivasan wrote: 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. |
#6
|
|||
|
|||
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. Govind wrote: 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. srinivasan wrote: 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. |
#7
|
|||
|
|||
"Govind" wrote: 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. Govind wrote: 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. srinivasan wrote: 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. 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 |
#8
|
|||
|
|||
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. -- Krishnakumar ------------------------------------------------------------------------ Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138 View this thread: http://www.excelforum.com/showthread...hreadid=386770 |
#9
|
|||
|
|||
"Krishnakumar" wrote: 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. -- Krishnakumar ------------------------------------------------------------------------ Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138 View this thread: http://www.excelforum.com/showthread...hreadid=386770 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 |
#10
|
|||
|
|||
Hi Srinivasan, But I want to know the logic behind 1+307 you used next to match function This value is the highest value that can be represented in Excel. Infact the value is 9.999999999999999E+307 HTH -- Krishnakumar ------------------------------------------------------------------------ Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138 View this thread: http://www.excelforum.com/showthread...hreadid=386770 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add one month to the previuos month | Excel Discussion (Misc queries) | |||
Month Year Date Format | Excel Worksheet Functions | |||
Accounting Month vs. Calendar Month | Excel Worksheet Functions | |||
How to extract month number from month name | Excel Discussion (Misc queries) | |||
PivotTable - Count by Month | Excel Worksheet Functions |