V
VJ7777
Please forgive me if I don't use proper Newsgroup
protocol. This is my first time.
I have developed a comprehensive dealership system using
Excel. I probably should have used Access but I don't
want to redo the system yet. Everything works swimmingly
except:
1. I use customer name to name each workbook (which
contains multiple worksheets of forms and data pertaining
to that customer):
For example: "Jones," "Smith" (or "Smith.John"), etc.
I have created reports which have a row for each customer
and ten or more columns of data for each customer. I get
the info for each column from the customer workbook and
worksheet using formulas like the one illustrated below
(ignore the complexity of the formula; I merely want to
automatically substitute "Smith" for "Jones" from a
separate master list of customer names):
Prospective Business Report (Sample Column Headings):
Name Product PurchaseDate Cost Sell etc., etc.
Example of one of many formulas:
IF('[Jones.xls]Buyer Progress Record'!
$B$12="failed","F",IF('[Jones.xls]Buyer Progress Record'!
$B$8="Cash Sale",'[Jones.xls]Buyer Progress Record'!
$B$8,IF('[Jones.xls]Buyer Progress Record'!
$B$11<>" ?","$"&'[Jones.xls]Buyer Progress
Record'!$B$11,IF('[Jones.xls]Buyer Progress Record'!
$B$10=" ?","?",IF('[Jones.xls]Buyer Progress
Record'!$B$10<$AA$2,"! ! ! ! !",'[Jones.xls]Buyer
Progress Record'!$B$10)))))
Everything works well so long as I copy down and replace
(i.e. replace "Jones" with "Smith") to add a new customer
row to the report. I cannot find a way to use a master
list of customer names to automatically change "Jones"
to "Smith" to "Green," etc.
2. I would like to produce a Contact Report by
Salesperson (using the above mentioned master list of
customer names) except that there would be multiple rows
for each customer. The source, Buyer Progress Record
worksheet, contains up to 40 lines of contacts in Rows 28
through 68 and, perhaps, only one or two rows might
contain data at any moment in time (Row 28 and Row 29;
the remaining rows will be used as time marches on). The
desired report would look like this and only print rows
for each customer that contain data:
Contact Report - Salesperson Murphy - Aug 17, 2004
Customer Name "Jones"
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Customer Name "Smith"
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Customer Name "Green"
Date of Contact Date of Next Contact Description
3. It would be nice to be able to add to the master list
of customer names automatically by having the computer
look to see if any new customer workbooks have been added
to the "Customers 2004" folder each day.
4. Finally, on another subject, in a cell, the
formula "=Today()" enters today's date in a cell
automatically. Is there a way enter today's date
automatically the first time a worksheet in a workbook is
handled but this date is never changed no matter how many
times the worksheet is modified?
Thank you in advance for any help you can render.
protocol. This is my first time.
I have developed a comprehensive dealership system using
Excel. I probably should have used Access but I don't
want to redo the system yet. Everything works swimmingly
except:
1. I use customer name to name each workbook (which
contains multiple worksheets of forms and data pertaining
to that customer):
For example: "Jones," "Smith" (or "Smith.John"), etc.
I have created reports which have a row for each customer
and ten or more columns of data for each customer. I get
the info for each column from the customer workbook and
worksheet using formulas like the one illustrated below
(ignore the complexity of the formula; I merely want to
automatically substitute "Smith" for "Jones" from a
separate master list of customer names):
Prospective Business Report (Sample Column Headings):
Name Product PurchaseDate Cost Sell etc., etc.
Example of one of many formulas:
IF('[Jones.xls]Buyer Progress Record'!
$B$12="failed","F",IF('[Jones.xls]Buyer Progress Record'!
$B$8="Cash Sale",'[Jones.xls]Buyer Progress Record'!
$B$8,IF('[Jones.xls]Buyer Progress Record'!
$B$11<>" ?","$"&'[Jones.xls]Buyer Progress
Record'!$B$11,IF('[Jones.xls]Buyer Progress Record'!
$B$10=" ?","?",IF('[Jones.xls]Buyer Progress
Record'!$B$10<$AA$2,"! ! ! ! !",'[Jones.xls]Buyer
Progress Record'!$B$10)))))
Everything works well so long as I copy down and replace
(i.e. replace "Jones" with "Smith") to add a new customer
row to the report. I cannot find a way to use a master
list of customer names to automatically change "Jones"
to "Smith" to "Green," etc.
2. I would like to produce a Contact Report by
Salesperson (using the above mentioned master list of
customer names) except that there would be multiple rows
for each customer. The source, Buyer Progress Record
worksheet, contains up to 40 lines of contacts in Rows 28
through 68 and, perhaps, only one or two rows might
contain data at any moment in time (Row 28 and Row 29;
the remaining rows will be used as time marches on). The
desired report would look like this and only print rows
for each customer that contain data:
Contact Report - Salesperson Murphy - Aug 17, 2004
Customer Name "Jones"
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Customer Name "Smith"
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Customer Name "Green"
Date of Contact Date of Next Contact Description
3. It would be nice to be able to add to the master list
of customer names automatically by having the computer
look to see if any new customer workbooks have been added
to the "Customers 2004" folder each day.
4. Finally, on another subject, in a cell, the
formula "=Today()" enters today's date in a cell
automatically. Is there a way enter today's date
automatically the first time a worksheet in a workbook is
handled but this date is never changed no matter how many
times the worksheet is modified?
Thank you in advance for any help you can render.