montly summary

[Guest]

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

 

[Ardus Petus]

Are your daily reports each on a separate sheet?

How are named the sheets?

You can use INDIRECT on monthly sheet.

HTH

 

[Arvi Laanemets]

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, Name>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 "yyyy.mmmm"
Into A3 enter the formula:
=IF(A2="","",IF(DATE(YEAR(A2),MONTH(A2)+1,1)>TODAY(),"",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 Months


Sheet 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 "yyyy.mmmm or "mmmm
yyyy", 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,"")
, and format as Custom "dd".
Copy A4 down for 31 rows.
Into cell B4 enter the formula
=IF(A4="","",SUMPRODUCT(--(Date=$A4),--(Parameter=B$3),Value))
Copy the formula from B4 to range B4:E34

It's all!