Mr. Rogers
New Member
- Joined
- Sep 18, 2002
- Messages
- 1
Be kind - I'm a newbie. Thanks for any help you can offer.
For a project that I'm managing, I have a spreadsheet with basic paid invoice information – date of check, amount paid, payee, account code. This is on Sheet 1.
I've used the DSUM function to create a report, on Sheet 2, that shows the total amount spent for each individual account. I would like to also show the amount spent each month for each individual account. There would be a column for each month from the inception of the project to the present time; Month 1 would be the starting month, Month 2 the second month, etc.
I was able to create a formula that compares the date of each check (as shown on Sheet 1) to the start date of the project and returns the current month. So, on Sheet 1, a new column gives the month number of each payment.
As noted, I know how to use DSUM on Sheet 2 to refer to a given project code and query the database on Sheet 1 for the sum of all payments under that code. I don't know how to query the database on Sheet 1 for the sum of all payments under a given code that fall within a given month. In other words, I think this is a sort within a sort.
I like the database functions because, in fact, I don't have to sort my data – I can enter it and the functions will pick up any additions or changes without my having to manually sort things again. This reduces the chance of the data changing and the report falling out of date due to a failure to manually sort the data and update and relevant formulas.
Is there a way to get this "sort within a sort" using the database functions? Just to be extra picky, I prefer not using macros.
Thanks again.
For a project that I'm managing, I have a spreadsheet with basic paid invoice information – date of check, amount paid, payee, account code. This is on Sheet 1.
I've used the DSUM function to create a report, on Sheet 2, that shows the total amount spent for each individual account. I would like to also show the amount spent each month for each individual account. There would be a column for each month from the inception of the project to the present time; Month 1 would be the starting month, Month 2 the second month, etc.
I was able to create a formula that compares the date of each check (as shown on Sheet 1) to the start date of the project and returns the current month. So, on Sheet 1, a new column gives the month number of each payment.
As noted, I know how to use DSUM on Sheet 2 to refer to a given project code and query the database on Sheet 1 for the sum of all payments under that code. I don't know how to query the database on Sheet 1 for the sum of all payments under a given code that fall within a given month. In other words, I think this is a sort within a sort.
I like the database functions because, in fact, I don't have to sort my data – I can enter it and the functions will pick up any additions or changes without my having to manually sort things again. This reduces the chance of the data changing and the report falling out of date due to a failure to manually sort the data and update and relevant formulas.
Is there a way to get this "sort within a sort" using the database functions? Just to be extra picky, I prefer not using macros.
Thanks again.
