formula needed to track dates event happened

I

ilmeaz

I am trying to automate a manual system used to track and bill plowing and
sanding services. I am looking for a formula which will match the customer
from the Customer History Sheet to the Plowing and Sanding Route Sheet, then
identify the dates containing ‘P’, ‘S’, and ‘N’ in the Plowing and Sanding
Sheet and list these dates in the Customer History Sheet in the respective
column beneath the appropriate Headers; listing the date multiple times
within the category if service was performed more than once on that date.
This is all being done manually at the moment so I can alter the layout of
the Customer History Sheet if that simplifies the process.


Plowing and Sanding Route Sheet contains route order sequence in column A,
customer ID in column B, Last Name in column C, First Name in column D,
Location in column E, Phone in column F, nothing in column G. Columns H
through DP are consecutive dates January 1st through December 31st.

Within the respective cell ‘P’ is entered to indicate plowing, ‘S’ to
indicate sanding, ‘N’ to indicate no charge for service performed. Cells
remain null when there was no activity. If a heavy storm hit it is possible
to have multiple ‘P’ and ‘S’ within the same date-cell.


Customer History Sheet contains Last Name and First Name concatenated in
cell B1, cell, Phone in cell B2, and Customer ID in cell D1. Headers are as
follows: Date Plowed in cell A4, Date Sanded in cell A5, Date N/C in cell
A6, with other billing information in following columns.

I am open to suggestions!

Thanks,
ilmeaz
 
O

Otto Moehrbach

No formula will do what you are asking. You will need to develop a VBA
project. If you wish, send me a file demonstrating what you have and what
you want Excel to do. Include several detail examples of what you are now
doing manually. Detail the steps you are doing in the process. Remember
that of the two of us, only you know anything about what you have and what
you want.
My email address is (e-mail address removed). Remove the "nop" from this
address. HTH Otto
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top