Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data summary
I trying to sort out a small spreadsheet for a charities collections and want
to keep as simple as possible as the users are novices. They are using Excel 2000. I set up a sheet that worked, but of necessity contained 26 columns ( one for each collectors takings) spread over 200 rows (Theoretically 366 max). In practical terms it wasn't user friendly. In essence it would be better to need only to enter a date, a name and a sum of money in 3 columns and have totals calculated in secondary sheets. My guess is that in order to get meaningful monthly totals against each of the 26 collectors will require some VBA code to pull that data into separate sheet. Can anyone guide me on data manipulation. I need to identify all instances of each of the names and total each name's takings for each calender month. thanks Pete |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data summary
My first thought is to create three columns: Name, Date, Amount. Enter the
information and then at the end of the month use auto filter. Data - Filter - Auto filter. Select the name and copy into another spreadsheet to sum or Sort by Name and then select work sheet and use SubTotal: Data - Subtotals. Select At a Change in Name, Sum, Amount. I am not sure if that is what you want to do, but it might work for you. Robert "Parish Pete" wrote: I trying to sort out a small spreadsheet for a charities collections and want to keep as simple as possible as the users are novices. They are using Excel 2000. I set up a sheet that worked, but of necessity contained 26 columns ( one for each collectors takings) spread over 200 rows (Theoretically 366 max). In practical terms it wasn't user friendly. In essence it would be better to need only to enter a date, a name and a sum of money in 3 columns and have totals calculated in secondary sheets. My guess is that in order to get meaningful monthly totals against each of the 26 collectors will require some VBA code to pull that data into separate sheet. Can anyone guide me on data manipulation. I need to identify all instances of each of the names and total each name's takings for each calender month. thanks Pete |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data summary
As you have suggested, use Sheet1 to enter the date (A), name (B) and
amount (C) in 3 columns. Then in Sheet2 you can set up a table showing the list of collector's names in A2:A27, and the months in B1:M1. Then in B2 you can have a SUMPRODUCT formula to give the amount against each name for each month. The formula would be something like: =SUMPRODUCT((Sheet1!$B$2:$B$500=$A2)*(TEXT(Sheet1! $A$2:$A$500,"mmm")=B $1)*(Sheet1!$C$2:$C$500)) which assumes you have just a 3-letter month in B1:M1, like Jan, Feb, Mar etc. Copy the formula across and down as required. You could use a pivot table to do the same. Hope this helps. Pete On Jan 26, 3:07*pm, Parish Pete wrote: I trying to sort out a small spreadsheet for a charities collections and want to keep as simple as possible as the users are novices. *They are using Excel 2000. I set up a sheet that worked, but of necessity contained 26 columns ( one for each collectors takings) spread over 200 rows (Theoretically 366 max).. * In practical terms it wasn't user friendly. * In essence it would be better to need only to enter a date, a name and a sum of money in 3 columns and have totals calculated in secondary sheets. My guess is that in order to get meaningful monthly totals against each of the 26 collectors will require some VBA code to pull that data into separate sheet. Can anyone guide me on data manipulation. *I need to identify all instances of each of the names and total each name's takings for each calender month. thanks Pete |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data summary
Thank you both for responding. Rob, the Data Subtotals function goes part
way but Pete's SUMPRODUCT does exactly what I need. I would never have found it! It opens the door to manipulating other bits of data from the same sheet on a similar basis. Given some data entry validation it should work like a charm. I'm really grateful for your help Regards Pete "Pete_UK" wrote: As you have suggested, use Sheet1 to enter the date (A), name (B) and amount (C) in 3 columns. Then in Sheet2 you can set up a table showing the list of collector's names in A2:A27, and the months in B1:M1. Then in B2 you can have a SUMPRODUCT formula to give the amount against each name for each month. The formula would be something like: =SUMPRODUCT((Sheet1!$B$2:$B$500=$A2)*(TEXT(Sheet1! $A$2:$A$500,"mmm")=B $1)*(Sheet1!$C$2:$C$500)) which assumes you have just a 3-letter month in B1:M1, like Jan, Feb, Mar etc. Copy the formula across and down as required. You could use a pivot table to do the same. Hope this helps. Pete On Jan 26, 3:07 pm, Parish Pete wrote: I trying to sort out a small spreadsheet for a charities collections and want to keep as simple as possible as the users are novices. They are using Excel 2000. I set up a sheet that worked, but of necessity contained 26 columns ( one for each collectors takings) spread over 200 rows (Theoretically 366 max).. In practical terms it wasn't user friendly. In essence it would be better to need only to enter a date, a name and a sum of money in 3 columns and have totals calculated in secondary sheets. My guess is that in order to get meaningful monthly totals against each of the 26 collectors will require some VBA code to pull that data into separate sheet. Can anyone guide me on data manipulation. I need to identify all instances of each of the names and total each name's takings for each calender month. thanks Pete |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data summary
You're welcome, Pete - I'm glad to hear that you can see how it could
be extended for other reports. One thing to be wary of is if you enter data beyond the first year, as the formula I gave you just adds all the data for a particular month. You will need to add another clause to the SP formula to distinguish the year, eg: *(YEAR(Sheet1!$A$2:$A$500)=2009) Then if you have another sheet for 2010 you can make the necessary change (or you could pick the year up from another cell, eg $A$1). Hope this helps. Pete On Jan 26, 7:09*pm, Parish Pete wrote: Thank you both for responding. *Rob, the Data Subtotals function goes part way but Pete's SUMPRODUCT does exactly what I need. *I would never have found it! It opens the door to manipulating other bits of data from the same sheet on a similar basis. *Given some data entry validation it should work like a charm. I'm really grateful for your help Regards Pete "Pete_UK" wrote: As you have suggested, use Sheet1 to enter the date (A), name (B) and amount (C) in 3 columns. Then in Sheet2 you can set up a table showing the list of collector's names in A2:A27, and the months in B1:M1. Then in B2 you can have a SUMPRODUCT formula to give the amount against each name for each month. The formula would be something like: =SUMPRODUCT((Sheet1!$B$2:$B$500=$A2)*(TEXT(Sheet1! $A$2:$A$500,"mmm")=B $1)*(Sheet1!$C$2:$C$500)) which assumes you have just a 3-letter month in B1:M1, like Jan, Feb, Mar etc. Copy the formula across and down as required. You could use a pivot table to do the same. Hope this helps. Pete On Jan 26, 3:07 pm, Parish Pete wrote: I trying to sort out a small spreadsheet for a charities collections and want to keep as simple as possible as the users are novices. *They are using Excel 2000. I set up a sheet that worked, but of necessity contained 26 columns ( one for each collectors takings) spread over 200 rows (Theoretically 366 max).. * In practical terms it wasn't user friendly. * In essence it would be better to need only to enter a date, a name and a sum of money in 3 columns and have totals calculated in secondary sheets. My guess is that in order to get meaningful monthly totals against each of the 26 collectors will require some VBA code to pull that data into separate sheet. Can anyone guide me on data manipulation. *I need to identify all instances of each of the names and total each name's takings for each calender month. thanks Pete- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summary data | Excel Discussion (Misc queries) | |||
Use detailed data in one worksheet to create summary data as chart source | Charts and Charting in Excel | |||
multi group with summary above with 1 overall summary line below | Excel Discussion (Misc queries) | |||
multi group with summary above with 1 overall summary line below | Excel Discussion (Misc queries) | |||
Summary of Data from a Range | Excel Discussion (Misc queries) |