Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for summary report
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. -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for summary report
On Apr 14, 6:08*pm, "tkraju via OfficeKB.com" <u16627@uwe wrote:
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. -- Message posted viahttp://www.officekb.com 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for summary report
On Apr 15, 2:12*pm, "tkraju via OfficeKB.com" <u16627@uwe wrote:
Thanks,but *I don't like Pivot Table. wrote: Every day new values are added to the end of database sheet. Col A *-- Col B *---Col C [quoted text clipped - 18 lines] -- Message posted viahttp://www.officekb.com 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 -- Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-programming/200904/1- Hide quoted text - - 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for summary report
Thanks,this sumproduct function worked very well.
wrote: 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 -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Customized Report Summary? | Excel Discussion (Misc queries) | |||
dynamic summary report | Excel Worksheet Functions | |||
summary report with duplicates | Excel Worksheet Functions | |||
Spreadsheet Summary Report | Excel Worksheet Functions | |||
Summary report questions | Excel Programming |