Excel problem

[iamjayesh]

I am attaching an excel file where i have a problem
In the file are 2 sheets, Main & second
I want to get data from second sheet to the main sheet by a formula by
which the amount in the total column will be posted in the second sheet
falling under various dates. I have done for 6 sept 2003 by way of
example

I do not know any formula by which i can do this automatically
Please help me

Attachment filename: example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=444742

 

[Frank Kabel]

Hi
one way: Try the following:
- on your first sheet ('Main') you have to duplicate the values for
column B so that each row is filled with a value. e.g. insert '1998' in
B4:B102
- you may also delete the subtotals in between (e.g. row 103)
- To get your second sheet two ways:
-> use a pivot table on your data. It will create this kind of
report automatically
-> with formulas: enter the following in cell D4 on your second
sheet
=SUMPRODU´CT((Main!$C$3:$C$999<=Data!D$3)*(Main!$C$3:$C$999>Data!D$3-7)
*(Main!$B$3:$B$999=Data!$C4),Main!$D$3:$D$999)
copy down and to the right