Hi all, I'm new to the forum, and have been an Excel user for about 2 years. I'm using Office 2003 on the PC (Windows XP SP2)
I've recently created a spreadsheet to track DVD rentals from online DVD mail-order companies. I've been able to develop formulas and macros to keep track of what DVDs I've reserved, which ones have been sent, returned by me, and then received back by the online company. Using this info I'm able to determine how many DVDs are in transit (coming or going) and what the turnaround time was (in days) between the time the DVD was sent and when the company reported it returned.
What I haven't been able to figure out is how to automatically tally the quantity of DVDs I was able to rent in a billing period (typically 30 days).
Here's how I have things set up:
I have "Worksheet 1" that keeps track of all the DVDs I've reserved/received, from which company, and dates sent/received, etc. I've created macros that allow me to sort the data by any column (Title, Company, Date Sent, Date Arrived, Date I Returned, Date Confirmed Returned by company).
I have "Worksheet 2" that I use to track my billing. It contains a "Start Date" and an "End Date" for the billing period, as well as the billing amount.
Using this data, combined with information from "Worksheet 1", I'm able to keep tabs on how much it's costing to rent a DVD (on average, based on total values), but I'd also like to be able to determine how many DVDs each company has sent me within the billing period in order to gauge mail efficiency, turnaround time, best bang for the buck, etc.
Is it possible to extract a tally of how many DVDs were sent by a company between two dates?
As a working example:
My billing period - August 8, 2005 (column A) to September 8, 2005 (column B)
Goal is to tally a 'counter' in a cell at the end of each row on my billing worksheet, which is based on a formula which looks at TWO columns on "Worksheet 1" (DVD Company and Date Sent) and compares this information to the company and date range in the two columns of the billing "Worksheet 2".
If a DVD was sent out by a company on August 10, 2005 based on data from Worksheet 1 (falling within the date range between cell A & cell B on a row in Worksheet 2) by company "X" (matching the billing company in cell C in Worksheet 2) then the counter (in a cell at the end of the same row on Worksheet 2) would be incremented by one. The formula would then continue to look for all other date matches within the date range AND company matches determined by the Start/End dates of the billing period as well as the DVD company matching the designated billing data; if a match was found then the number would continue to be incremented.
The end result I'm looking for is to have a dynamically created tally of how many DVDs I was able to rent monthly from each company, derived by the "Sent Dates" relation to the date range of the billing period.
If I can figure out a way to do this 'experimental' project then I also have a use for it on another project I have in mind for monthly customer job-tracking for my business purposes, which would prove to be a much more valuable and worthwhile task.
I've exhausted every avenue in trying to resolve this predicament, and maybe it's not possible... or perhaps it's so simple that I've overlooked the obvious?!
All help will be greatly appreciated.
Illusion
I've recently created a spreadsheet to track DVD rentals from online DVD mail-order companies. I've been able to develop formulas and macros to keep track of what DVDs I've reserved, which ones have been sent, returned by me, and then received back by the online company. Using this info I'm able to determine how many DVDs are in transit (coming or going) and what the turnaround time was (in days) between the time the DVD was sent and when the company reported it returned.
What I haven't been able to figure out is how to automatically tally the quantity of DVDs I was able to rent in a billing period (typically 30 days).
Here's how I have things set up:
I have "Worksheet 1" that keeps track of all the DVDs I've reserved/received, from which company, and dates sent/received, etc. I've created macros that allow me to sort the data by any column (Title, Company, Date Sent, Date Arrived, Date I Returned, Date Confirmed Returned by company).
I have "Worksheet 2" that I use to track my billing. It contains a "Start Date" and an "End Date" for the billing period, as well as the billing amount.
Using this data, combined with information from "Worksheet 1", I'm able to keep tabs on how much it's costing to rent a DVD (on average, based on total values), but I'd also like to be able to determine how many DVDs each company has sent me within the billing period in order to gauge mail efficiency, turnaround time, best bang for the buck, etc.
Is it possible to extract a tally of how many DVDs were sent by a company between two dates?
As a working example:
My billing period - August 8, 2005 (column A) to September 8, 2005 (column B)
Goal is to tally a 'counter' in a cell at the end of each row on my billing worksheet, which is based on a formula which looks at TWO columns on "Worksheet 1" (DVD Company and Date Sent) and compares this information to the company and date range in the two columns of the billing "Worksheet 2".
If a DVD was sent out by a company on August 10, 2005 based on data from Worksheet 1 (falling within the date range between cell A & cell B on a row in Worksheet 2) by company "X" (matching the billing company in cell C in Worksheet 2) then the counter (in a cell at the end of the same row on Worksheet 2) would be incremented by one. The formula would then continue to look for all other date matches within the date range AND company matches determined by the Start/End dates of the billing period as well as the DVD company matching the designated billing data; if a match was found then the number would continue to be incremented.
The end result I'm looking for is to have a dynamically created tally of how many DVDs I was able to rent monthly from each company, derived by the "Sent Dates" relation to the date range of the billing period.
If I can figure out a way to do this 'experimental' project then I also have a use for it on another project I have in mind for monthly customer job-tracking for my business purposes, which would prove to be a much more valuable and worthwhile task.
I've exhausted every avenue in trying to resolve this predicament, and maybe it's not possible... or perhaps it's so simple that I've overlooked the obvious?!
All help will be greatly appreciated.
Illusion
