software

sir, i m preparing the a daily report, in which i add the figure, in the
last of month , i have to prepare a monthly summary , that i prepare
manually, in the summary,

for example , this month of april, i give the date in the date cell,all
data fill into the daily report. now in monthly summary report ( i have
already prepare the monthly report by date wise from 1~30),
like this :

for example
this is my daily summary
date 4/1/06
this is my data that i write into the cells
parameters value
count 12.1
cv 1.2
clsp 2340
cvb 2.5

now in montly summary,

date count cv clsp cvb
4/1 12.1 1.2 2340 2.5
4/2 12.5 1.4 2340 3.1
4/3
4/4
up to
30/4

now i want my 4/1 values automatic add into the all parameters , and
next day when date change, in montly summary automatic data transfer to
4/2 's parametes,

is it possible in excel or not

Are your daily reports each on a separate sheet?

How are named the sheets?

You can use INDIRECT on monthly sheet.

HTH
--
AP

quot;imranquot; gt; a écrit dans le message de
news
gt; sir, i m preparing the a daily report, in which i add the figure, in the
gt; last of month , i have to prepare a monthly summary , that i prepare
gt; manually, in the summary,
gt;
gt; for example , this month of april, i give the date in the date cell,all
gt; data fill into the daily report. now in monthly summary report ( i have
gt; already prepare the monthly report by date wise from 1~30),
gt; like this :
gt;
gt; for example
gt; this is my daily summary
gt; date 4/1/06
gt; this is my data that i write into the cells
gt; parameters value
gt; count 12.1
gt; cv 1.2
gt; clsp 2340
gt; cvb 2.5
gt;
gt; now in montly summary,
gt;
gt; date count cv clsp cvb
gt; 4/1 12.1 1.2 2340 2.5
gt; 4/2 12.5 1.4 2340 3.1
gt; 4/3
gt; 4/4
gt; up to
gt; 30/4
gt;
gt; now i want my 4/1 values automatic add into the all parameters , and
gt; next day when date change, in montly summary automatic data transfer to
gt; 4/2 's parametes,
gt;
gt; is it possible in excel or not
Hi

Redesign your workbook in following way:

Sheet Data:
Date Parameter Value
4/1/06 count 12.1
4/1/06 cv 1.2
4/1/06 clsp 2340
.....

Define named ranges (from Insert menu, Namegt;Define) p.e.
Date=OFFSET(Data!$A$1,1,,COUNT($A:$A),1)
Parameter=OFFSET(Data!$B$1,1,,COUNT($A:$A),1)
Value=OFFSET(Data!$C$1,1,,COUNT($A:$A),1)

Sheet Months:
Month

Into A2 enter the 1st of start month in any valid date format, like 1/1/06,
and format like as Custom quot;yyyy.mmmmquot;
Into A3 enter the formula:
=IF(A2=quot;quot;,quot;quot;,IF(DATE(YEAR(A2),MONTH(A2) 1,1)gt;TODAY (),quot;quot;,DATE(YEAR(A2),MONTH(A2) 1,1)))
and format like A2.
Copy A3 down for some resonable amount of rows.

Define a named range Months
=OFFSET(Months!$A$1,1,,COUNT($A:$A),1)

Hide sheet MonthsSheet MonthlyRep:
For some cell, p.e. B1, apply data validation list (From Data menu select
Validation, etc.), with source
=Months
, i.e. the list refers to named range created befor. Now you can select the
any month from start one up to current into this cell. Format the cell in
any valid date format - reasonable will be formats like quot;yyyy.mmmm or quot;mmmm
yyyyquot;, etc.

Into row 3 enter table headers
Date Count Cv Clsp Cvb

Into A4 enter the formula
=IF(MONTH($B$1 ROW()-4)=MONTH($B$1),$B$1 ROW()-4,quot;quot;)
, and format as Custom quot;ddquot;.
Copy A4 down for 31 rows.
Into cell B4 enter the formula
=IF(A4=quot;quot;,quot;quot;,SUMPRODUCT(--(Date=$A4),--(Parameter=B$3),Value))
Copy the formula from B4 to range B4:E34

It's all!

--
Arvi Laanemets
( My real mail address: arvi.laanemetslt;atgt;tarkon.ee )
quot;imranquot; gt; wrote in message
news
gt; sir, i m preparing the a daily report, in which i add the figure, in the
gt; last of month , i have to prepare a monthly summary , that i prepare
gt; manually, in the summary,
gt;
gt; for example , this month of april, i give the date in the date cell,all
gt; data fill into the daily report. now in monthly summary report ( i have
gt; already prepare the monthly report by date wise from 1~30),
gt; like this :
gt;
gt; for example
gt; this is my daily summary
gt; date 4/1/06
gt; this is my data that i write into the cells
gt; parameters value
gt; count 12.1
gt; cv 1.2
gt; clsp 2340
gt; cvb 2.5
gt;
gt; now in montly summary,
gt;
gt; date count cv clsp cvb
gt; 4/1 12.1 1.2 2340 2.5
gt; 4/2 12.5 1.4 2340 3.1
gt; 4/3
gt; 4/4
gt; up to
gt; 30/4
gt;
gt; now i want my 4/1 values automatic add into the all parameters , and
gt; next day when date change, in montly summary automatic data transfer to
gt; 4/2 's parametes,
gt;
gt; is it possible in excel or not