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
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
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.