Code for summary report

  • Thread starter tkraju via OfficeKB.com
  • Start date
T

tkraju via OfficeKB.com

Every day new values are added to the end of database sheet.
Col A -- Col B ---Col C
john-----03/29/09--$45
mary----03/30/09---$205
mary --- 04/01/09- $250
john ----04/04/09 - $ 100
Bill ----04/04/09-- $ 25
mary-----04/05/09---$50
mary ----04/06/09--$100
john -----04/10/09---$ 35

like this new records are added everytime.
in Col D1,If i enter Apr-09 output will be like this
Col D
Apr-09
mary ----$400
john------$135
Bill-------$25
Code from any body for this highly appreciated.
 
M

meh2030

Every day new values are added to the end of database sheet.
Col A  -- Col B  ---Col C
john-----03/29/09--$45
mary----03/30/09---$205
mary  --- 04/01/09- $250
john   ----04/04/09 - $ 100
Bill    ----04/04/09-- $ 25
mary-----04/05/09---$50
mary  ----04/06/09--$100
john -----04/10/09---$ 35

like this new records are added everytime.
 in Col D1,If i enter Apr-09 output will be like this
Col D
Apr-09
mary ----$400
john------$135
Bill-------$25
 Code from any body for this highly appreciated.

tkraju,

I think I follow what you are trying to do. Have you tried using a
pivot table? You can set up the pivot table range as a named range
(i.e. in Excel 2003: Insert | Name | Define; in Excel 2007: Formulas |
Define Name), and then simply change the "Refers To" when your data
set expands to include all of the newly added cells. Of course, you
can design the pivot table however you like, but you can try the date
as the Page Field, the names as the Row Field, and the dollar value as
the Data Item.

Best,

Matthew Herbert
 
T

tkraju via OfficeKB.com

Thanks,but I don't like Pivot Table.

Every day new values are added to the end of database sheet.
Col A  -- Col B  ---Col C
[quoted text clipped - 18 lines]
tkraju,

I think I follow what you are trying to do. Have you tried using a
pivot table? You can set up the pivot table range as a named range
(i.e. in Excel 2003: Insert | Name | Define; in Excel 2007: Formulas |
Define Name), and then simply change the "Refers To" when your data
set expands to include all of the newly added cells. Of course, you
can design the pivot table however you like, but you can try the date
as the Page Field, the names as the Row Field, and the dollar value as
the Data Item.

Best,

Matthew Herbert
 
M

meh2030

Thanks,but  I don't like Pivot Table.





Every day new values are added to the end of database sheet.
Col A  -- Col B  ---Col C
[quoted text clipped - 18 lines]

I think I follow what you are trying to do.  Have you tried using a
pivot table?  You can set up the pivot table range as a named range
(i.e. in Excel 2003: Insert | Name | Define; in Excel 2007: Formulas |
Define Name), and then simply change the "Refers To" when your data
set expands to include all of the newly added cells.  Of course, you
can design the pivot table however you like, but you can try the date
as the Page Field, the names as the Row Field, and the dollar value as
the Data Item.

Matthew Herbert

tkraju,

Try using the SUMPRODUCT function. I placed your data into a
spreadsheet as follows: Names in Column A, starting in A1; Dates in
Column B, starting in B1; and Dollar Values in Column C, starting in
C1. I then placed a hard-coded date in D1 (i.e. 4/1/09) and a formula
in D2 (i.e. =EOMONTH(D1, 0)). I then insert the following data into
the respective cells: Mary, John, and Bill in cells D2, D3, and D4.
Lastly, I placed the following formula in cell E2 and copied it down:
=SUMPRODUCT(($A$1:$A$8=D2)*($B$1:$B$8<=$E$1)*($B$1:$B$8>=$D$1)*($C$1:$C
$8)).

A1:A8 refers to the Names
B1:B8 refers to the Dates
C1:C8 refers to the Values
D1 refers to the first day of the month
E1 refers to the last day of the month

You can create named ranges for the Names, Dates, and Values instead
of referencing the cells, if you so desire. Whenever new data is
added, simply change the Refers To for each range. The formula will
work only if the Names, Dates, and Values arrays are the same size.

Best,

Matt
 
T

tkraju via OfficeKB.com

Thanks,this sumproduct function worked very well.

Thanks,but  I don't like Pivot Table.
[quoted text clipped - 24 lines]
- Show quoted text -

tkraju,

Try using the SUMPRODUCT function. I placed your data into a
spreadsheet as follows: Names in Column A, starting in A1; Dates in
Column B, starting in B1; and Dollar Values in Column C, starting in
C1. I then placed a hard-coded date in D1 (i.e. 4/1/09) and a formula
in D2 (i.e. =EOMONTH(D1, 0)). I then insert the following data into
the respective cells: Mary, John, and Bill in cells D2, D3, and D4.
Lastly, I placed the following formula in cell E2 and copied it down:
=SUMPRODUCT(($A$1:$A$8=D2)*($B$1:$B$8<=$E$1)*($B$1:$B$8>=$D$1)*($C$1:$C
$8)).

A1:A8 refers to the Names
B1:B8 refers to the Dates
C1:C8 refers to the Values
D1 refers to the first day of the month
E1 refers to the last day of the month

You can create named ranges for the Names, Dates, and Values instead
of referencing the cells, if you so desire. Whenever new data is
added, simply change the Refers To for each range. The formula will
work only if the Names, Dates, and Values arrays are the same size.

Best,

Matt
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top