Making Excel generate Access-Like Reports

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.

Hi

can't help (yet) with the other questions but
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?

you can put code against each sheet (itself) in the vbe window similar to
Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell address as required
Range("A1").Value = Now
End If
End Sub

or

Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell address as required
Range("A1").Value = Format(Now, "dd mmmm yyyy")
End If
End Sub

Hope this helps
Cheers
JulieD



"VJ7777" wrote in message
...
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.

-----Original Message-----
Hi

can't help (yet) with the other questions but
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?

you can put code against each sheet (itself) in the vbe
window similar to
Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell address as
required
Range("A1").Value = Now
End If
End Sub

or

Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell address as
required
Range("A1").Value = Format(Now, "dd mmmm yyyy")
End If
End Sub

Hope this helps
Cheers
JulieD



"VJ7777" wrote in
message
...
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.



Hi, Julie:
Thank you , very much for your response. I must confess
complete lack of knowledge on how to start implementing
your code. I have been in the computer industry since
the beginning (about the time of Eniac (sp?). I taught
myself how to read and modify Fortran code when I was
product manager of a capital investment evaluation model
for Fortune 500 companies using Computer Sciences' time
sharing system. I taught myself how to program in Basic
when I had the first Apple computer and, for fun,
designed my own General Ledger system. On the other
hand, I have pulled out the Visual Basic book multiple
times and, as I did tonight, asked myself why I should
punish myself when Microsoft's explanation never seems to
explain a place to begin.

If it isn't asking too much, could you please tell me
what steps to take to simply make your code work in
cell "B2" of worksheet "New Input" of
workbook "aaCustomer." Maybe, if you do that, I can get
the concept of how to begin to use VB - certainly I have
never gotten a clue from the Microsoft VB book.

Your message seemed to indicate that you might have
answers to my other questions - and I suspect VB will be
a major part of your answer. If so, I will need to know
how to intergrate them into Excel; your response
regarding to how to implement this will be very necessary
to understanding your other solutions.
Thanks again,
Vince

Hi Vince

to place the code in the workbook, right mouse click on any sheet tab and
choose view code. This will display the VB Editor ... on the top left there
should be a little area with folders & sheets & the name of your workbook in
bold - if not, choose View / Project Explorer from the menu.

under the project with your workbook name in bold, you will see "NewInput"
and the rest of your sheets - double click on the NewInput one, and a white
piece of paper should appear on the right hand side of the screen , up the
top are two drop down boxes, choose
worksheet
from the lhs one and Activate from the right
then the words

Private Sub Worksheet_Activate()
End Sub

should appear .. place my code between these two lines.
Change the A1 to B2
You can "switch" between the code window & your workbook by using Alt & F1l
.... switch back, ensure B2 on the NewInput sheet has nothing in it, switch
to another sheet, come back to the NewInput sheet the date should be filled
in. Now you can test out both bits of code i gave you to decide which one
is the one you want.

(by the way, it is always a good idea to make a backup testing any code etc)

With regards to your other questions, i'm still having a look at them, but
i've got a few other things on at the moment and havent' really had time to
sit down & think about them properly. If you haven't done so already you
might like to do a search of google (groups.google.com - advanced search:
search string changing workbook names formulas ... search groups
microsoft.public.excel* ), i would do it for you and see if i come up with
anything but my internet explorer's not working at the moment

Hope this helps
Cheers
JulieD


wrote in message
...

-----Original Message-----
Hi

can't help (yet) with the other questions but
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?

you can put code against each sheet (itself) in the vbe
window similar to
Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell address as
required
Range("A1").Value = Now
End If
End Sub

or

Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell address as
required
Range("A1").Value = Format(Now, "dd mmmm yyyy")
End If
End Sub

Hope this helps
Cheers
JulieD



"VJ7777" wrote in
message
...
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.



Hi, Julie:
Thank you , very much for your response. I must confess
complete lack of knowledge on how to start implementing
your code. I have been in the computer industry since
the beginning (about th etimeofEniac.Itaught
myself how to read and modify Fortran code when I was
product manager of a capital investment evaluation model
for Fortune 500 companies using Computer Sciences' time
sharing system. I taught myself how to program in Basic
when I had the first Apple computer and, for fun,
designed my own General Ledger system. On the other
hand, I have pulled out the Visual Basic book multiple
times and, as I did tonight, asked myself why I should
punish myself when Microsoft's explanation never seems to
explain a place to begin.

If it isn't asking too much, could you please tell me
what steps to take to simply make your code work in
cell "B2" of worksheet "New Input" of
workbook "aaCustomer." Maybe, if you do that, I can get
the concept of how to begin to use VB - certainly I have
never gotten a clue from the Microsoft VB book.

Your message seemed to indicate that you might have
answers to my other questions - and I suspect VB will be
a major part of your answer. If so, I will need to know
how to intergrate them into Excel; your response
regarding to how to implement this will be very necessary
to understanding your other solutions.
Thanks again,
Vince

-----Original Message-----
Hi Vince

to place the code in the workbook, right mouse click on
any sheet tab and
choose view code. This will display the VB Editor ...
on the top left there
should be a little area with folders & sheets & the name
of your workbook in
bold - if not, choose View / Project Explorer from the
menu.

under the project with your workbook name in bold, you
will see "NewInput"
and the rest of your sheets - double click on the
NewInput one, and a white
piece of paper should appear on the right hand side of
the screen , up the
top are two drop down boxes, choose
worksheet
from the lhs one and Activate from the right
then the words

Private Sub Worksheet_Activate()
End Sub

should appear .. place my code between these two lines.
Change the A1 to B2
You can "switch" between the code window & your workbook
by using Alt & F1l
.... switch back, ensure B2 on the NewInput sheet has
nothing in it, switch
to another sheet, come back to the NewInput sheet the
date should be filled
in. Now you can test out both bits of code i gave you
to decide which one
is the one you want.

(by the way, it is always a good idea to make a backup
testing any code etc)

With regards to your other questions, i'm still having a
look at them, but
i've got a few other things on at the moment and havent'
really had time to
sit down & think about them properly. If you haven't
done so already you
might like to do a search of google (groups.google.com -
advanced search:
search string changing workbook names formulas ...
search groups
microsoft.public.excel* ), i would do it for you and see
if i come up with
anything but my internet explorer's not working at the
moment

Hope this helps
Cheers
JulieD


wrote in message
...

-----Original Message-----
Hi

can't help (yet) with the other questions but
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?

you can put code against each sheet (itself) in the
vbe
window similar to
Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell address
as
required
Range("A1").Value = Now
End If
End Sub

or

Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell address as
required
Range("A1").Value = Format(Now, "dd mmmm yyyy")
End If
End Sub

Hope this helps
Cheers
JulieD



"VJ7777" wrote
in
message
...
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.



Hi, Julie:
Thank you , very much for your response. I must
confess
complete lack of knowledge on how to start implementing
your code. I have been in the computer industry since
the beginning (about th
etimeofEniac.Itaught
myself how to read and modify Fortran code when I was
product manager of a capital investment evaluation
model
for Fortune 500 companies using Computer Sciences' time
sharing system. I taught myself how to program in
Basic
when I had the first Apple computer and, for fun,
designed my own General Ledger system. On the other
hand, I have pulled out the Visual Basic book multiple
times and, as I did tonight, asked myself why I should
punish myself when Microsoft's explanation never seems
to
explain a place to begin.

If it isn't asking too much, could you please tell me
what steps to take to simply make your code work in
cell "B2" of worksheet "New Input" of
workbook "aaCustomer." Maybe, if you do that, I can
get
the concept of how to begin to use VB - certainly I
have
never gotten a clue from the Microsoft VB book.

Your message seemed to indicate that you might have
answers to my other questions - and I suspect VB will
be
a major part of your answer. If so, I will need to
know
how to intergrate them into Excel; your response
regarding to how to implement this will be very
necessary
to understanding your other solutions.
Thanks again,
Vince



.Julie, thanks a million. I'll try the google search
later today. One quick question: The date appears OK
after I switch to another worksheet and back again, but,
suppose one doesn't go to another worksheet in today's
data entry session; how does today's date get into the
cell. I've tried saving the workbook and re-opening it
but the cell remains blank unless I switch to another
worksheet and back again
Again, I really appreciate your help!!!
Vince

Hi Vince

you can also put code against the THISWORKBOOK object on the workbook_open
event (double click on ThisWorkbook in the VBE window, choose Workbook from
the lhs drop down & Open from the right),
you'll need to edit the code to:

If IsEmpty(Sheets("NewInput").Range("B2")) then
Sheets("NewInput").Range("B2").Value = Format(Now, "dd mmmm yyyy")
End If

hope this helps
Cheers
JulieD


"VJ7777" wrote in message
...

-----Original Message-----
Hi Vince

to place the code in the workbook, right mouse click on
any sheet tab and
choose view code. This will display the VB Editor ...
on the top left there
should be a little area with folders & sheets & the name
of your workbook in
bold - if not, choose View / Project Explorer from the
menu.

under the project with your workbook name in bold, you
will see "NewInput"
and the rest of your sheets - double click on the
NewInput one, and a white
piece of paper should appear on the right hand side of
the screen , up the
top are two drop down boxes, choose
worksheet
from the lhs one and Activate from the right
then the words

Private Sub Worksheet_Activate()
End Sub

should appear .. place my code between these two lines.
Change the A1 to B2
You can "switch" between the code window & your workbook
by using Alt & F1l
.... switch back, ensure B2 on the NewInput sheet has
nothing in it, switch
to another sheet, come back to the NewInput sheet the
date should be filled
in. Now you can test out both bits of code i gave you
to decide which one
is the one you want.

(by the way, it is always a good idea to make a backup
testing any code etc)

With regards to your other questions, i'm still having a
look at them, but
i've got a few other things on at the moment and havent'
really had time to
sit down & think about them properly. If you haven't
done so already you
might like to do a search of google (groups.google.com -
advanced search:
search string changing workbook names formulas ...
search groups
microsoft.public.excel* ), i would do it for you and see
if i come up with
anything but my internet explorer's not working at the
moment

Hope this helps
Cheers
JulieD


wrote in message
...

-----Original Message-----
Hi

can't help (yet) with the other questions but
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?

you can put code against each sheet (itself) in the
vbe
window similar to
Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell address
as
required
Range("A1").Value = Now
End If
End Sub

or

Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell address as
required
Range("A1").Value = Format(Now, "dd mmmm yyyy")
End If
End Sub

Hope this helps
Cheers
JulieD



"VJ7777" wrote
in
message
...
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.



Hi, Julie:
Thank you , very much for your response. I must
confess
complete lack of knowledge on how to start implementing
your code. I have been in the computer industry since
the beginning (about th
etimeofEniac.Itaught
myself how to read and modify Fortran code when I was
product manager of a capital investment evaluation
model
for Fortune 500 companies using Computer Sciences' time
sharing system. I taught myself how to program in
Basic
when I had the first Apple computer and, for fun,
designed my own General Ledger system. On the other
hand, I have pulled out the Visual Basic book multiple
times and, as I did tonight, asked myself why I should
punish myself when Microsoft's explanation never seems
to
explain a place to begin.

If it isn't asking too much, could you please tell me
what steps to take to simply make your code work in
cell "B2" of worksheet "New Input" of
workbook "aaCustomer." Maybe, if you do that, I can
get
the concept of how to begin to use VB - certainly I
have
never gotten a clue from the Microsoft VB book.

Your message seemed to indicate that you might have
answers to my other questions - and I suspect VB will
be
a major part of your answer. If so, I will need to
know
how to intergrate them into Excel; your response
regarding to how to implement this will be very
necessary
to understanding your other solutions.
Thanks again,
Vince



.Julie, thanks a million. I'll try the google search
later today. One quick question: The date appears OK
after I switch to another worksheet and back again, but,
suppose one doesn't go to another worksheet in today's
data entry session; how does today's date get into the
cell. I've tried saving the workbook and re-opening it
but the cell remains blank unless I switch to another
worksheet and back again
Again, I really appreciate your help!!!
Vince

-----Original Message-----
Hi Vince

you can also put code against the THISWORKBOOK object on
the workbook_open
event (double click on ThisWorkbook in the VBE window,
choose Workbook from
the lhs drop down & Open from the right),
you'll need to edit the code to:

If IsEmpty(Sheets("NewInput").Range("B2")) then
Sheets("NewInput").Range("B2").Value = Format
(Now, "dd mmmm yyyy")
End If

hope this helps
Cheers
JulieD


"VJ7777" wrote in
message
...

-----Original Message-----
Hi Vince

to place the code in the workbook, right mouse click
on
any sheet tab and
choose view code. This will display the VB Editor ...
on the top left there
should be a little area with folders & sheets & the
name
of your workbook in
bold - if not, choose View / Project Explorer from the
menu.

under the project with your workbook name in bold, you
will see "NewInput"
and the rest of your sheets - double click on the
NewInput one, and a white
piece of paper should appear on the right hand side of
the screen , up the
top are two drop down boxes, choose
worksheet
from the lhs one and Activate from the right
then the words

Private Sub Worksheet_Activate()
End Sub

should appear .. place my code between these two
lines.
Change the A1 to B2
You can "switch" between the code window & your
workbook
by using Alt & F1l
.... switch back, ensure B2 on the NewInput sheet has
nothing in it, switch
to another sheet, come back to the NewInput sheet the
date should be filled
in. Now you can test out both bits of code i gave you
to decide which one
is the one you want.

(by the way, it is always a good idea to make a backup
testing any code etc)

With regards to your other questions, i'm still
having a
look at them, but
i've got a few other things on at the moment and
havent'
really had time to
sit down & think about them properly. If you haven't
done so already you
might like to do a search of google
(groups.google.com -
advanced search:
search string changing workbook names formulas ...
search groups
microsoft.public.excel* ), i would do it for you and
see
if i come up with
anything but my internet explorer's not working at the
moment

Hope this helps
Cheers
JulieD


wrote in message
...

-----Original Message-----
Hi

can't help (yet) with the other questions but
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?

you can put code against each sheet (itself) in the
vbe
window similar to
Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell
address
as
required
Range("A1").Value = Now
End If
End Sub

or

Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell address
as
required
Range("A1").Value = Format(Now, "dd mmmm yyyy")
End If
End Sub

Hope this helps
Cheers
JulieD



"VJ7777"
wrote
in
message
...
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.



Hi, Julie:
Thank you , very much for your response. I must
confess
complete lack of knowledge on how to start
implementing
your code. I have been in the computer industry
since
the beginning (about th
etimeofEniac.Itaught
myself how to read and modify Fortran code when I
was
product manager of a capital investment evaluation
model
for Fortune 500 companies using Computer Sciences'
time
sharing system. I taught myself how to program in
Basic
when I had the first Apple computer and, for fun,
designed my own General Ledger system. On the other
hand, I have pulled out the Visual Basic book
multiple
times and, as I did tonight, asked myself why I
should
punish myself when Microsoft's explanation never
seems
to
explain a place to begin.

If it isn't asking too much, could you please tell
me
what steps to take to simply make your code work in
cell "B2" of worksheet "New Input" of
workbook "aaCustomer." Maybe, if you do that, I can
get
the concept of how to begin to use VB - certainly I
have
never gotten a clue from the Microsoft VB book.

Your message seemed to indicate that you might have
answers to my other questions - and I suspect VB
will
be
a major part of your answer. If so, I will need to
know
how to intergrate them into Excel; your response
regarding to how to implement this will be very
necessary
to understanding your other solutions.
Thanks again,
Vince



.Julie, thanks a million. I'll try the google search
later today. One quick question: The date appears OK
after I switch to another worksheet and back again,
but,
suppose one doesn't go to another worksheet in today's
data entry session; how does today's date get into the
cell. I've tried saving the workbook and re-opening it
but the cell remains blank unless I switch to another
worksheet and back again
Again, I really appreciate your help!!!
Vince




.Hi Julie:
I'm afraid I am asking too much of you - it isn't your
task in life to train me in VB. I scan the book and it
seems to avoid simple tasks. I've tried Google for a
list of commands (for example: goto - or moveto, etc. -
so I can say 'if whatever, goto "a1")' and cannot find
such a list. If you point me in the right direction I'll
go there and study. Having started life as a bookkeeping
machine salesperson I am strong on applications and look
to programming as a list of simple commands to copy into
a program to get my job done.

Having said that, the code you gave me generated "Run-
time error '9': Subscript out of range." Here is the
code I entered:
Private Sub Workbook_Open()
If IsEmpty(Sheets("NewInput").Range("B2")) Then
Sheets("NewInput").Range("B2").Value = Format(Now, "dd
mmmm yyyy")
End If
End Sub

If you will help one more time with the above questions
I'll go it alone from that point. But will hope you can
solve the first three questions in my original posting
when you get the time. (I'll do the Google search you
recommended before or during the Labor Day weekend.)

Again, thank you very much.
Vince

"VJ7777" wrote in message
...

-----Original Message-----
Hi Vince

you can also put code against the THISWORKBOOK object on
the workbook_open
event (double click on ThisWorkbook in the VBE window,
choose Workbook from
the lhs drop down & Open from the right),
you'll need to edit the code to:

If IsEmpty(Sheets("NewInput").Range("B2")) then
Sheets("NewInput").Range("B2").Value = Format
(Now, "dd mmmm yyyy")
End If

hope this helps
Cheers
JulieD


"VJ7777" wrote in
message
...

-----Original Message-----
Hi Vince

to place the code in the workbook, right mouse click
on
any sheet tab and
choose view code. This will display the VB Editor ...
on the top left there
should be a little area with folders & sheets & the
name
of your workbook in
bold - if not, choose View / Project Explorer from the
menu.

under the project with your workbook name in bold, you
will see "NewInput"
and the rest of your sheets - double click on the
NewInput one, and a white
piece of paper should appear on the right hand side of
the screen , up the
top are two drop down boxes, choose
worksheet
from the lhs one and Activate from the right
then the words

Private Sub Worksheet_Activate()
End Sub

should appear .. place my code between these two
lines.
Change the A1 to B2
You can "switch" between the code window & your
workbook
by using Alt & F1l
.... switch back, ensure B2 on the NewInput sheet has
nothing in it, switch
to another sheet, come back to the NewInput sheet the
date should be filled
in. Now you can test out both bits of code i gave you
to decide which one
is the one you want.

(by the way, it is always a good idea to make a backup
testing any code etc)

With regards to your other questions, i'm still
having a
look at them, but
i've got a few other things on at the moment and
havent'
really had time to
sit down & think about them properly. If you haven't
done so already you
might like to do a search of google
(groups.google.com -
advanced search:
search string changing workbook names formulas ...
search groups
microsoft.public.excel* ), i would do it for you and
see
if i come up with
anything but my internet explorer's not working at the
moment

Hope this helps
Cheers
JulieD


wrote in message
...

-----Original Message-----
Hi

can't help (yet) with the other questions but
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?

you can put code against each sheet (itself) in the
vbe
window similar to
Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell
address
as
required
Range("A1").Value = Now
End If
End Sub

or

Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell address
as
required
Range("A1").Value = Format(Now, "dd mmmm yyyy")
End If
End Sub

Hope this helps
Cheers
JulieD



"VJ7777"
wrote
in
message
...
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.



Hi, Julie:
Thank you , very much for your response. I must
confess
complete lack of knowledge on how to start
implementing
your code. I have been in the computer industry
since
the beginning (about th
etimeofEniac.Itaught
myself how to read and modify Fortran code when I
was
product manager of a capital investment evaluation
model
for Fortune 500 companies using Computer Sciences'
time
sharing system. I taught myself how to program in
Basic
when I had the first Apple computer and, for fun,
designed my own General Ledger system. On the other
hand, I have pulled out the Visual Basic book
multiple
times and, as I did tonight, asked myself why I
should
punish myself when Microsoft's explanation never
seems
to
explain a place to begin.

If it isn't asking too much, could you please tell
me
what steps to take to simply make your code work in
cell "B2" of worksheet "New Input" of
workbook "aaCustomer." Maybe, if you do that, I can
get
the concept of how to begin to use VB - certainly I
have
never gotten a clue from the Microsoft VB book.

Your message seemed to indicate that you might have
answers to my other questions - and I suspect VB
will
be
a major part of your answer. If so, I will need to
know
how to intergrate them into Excel; your response
regarding to how to implement this will be very
necessary
to understanding your other solutions.
Thanks again,
Vince



.Julie, thanks a million. I'll try the google search
later today. One quick question: The date appears OK
after I switch to another worksheet and back again,
but,
suppose one doesn't go to another worksheet in today's
data entry session; how does today's date get into the
cell. I've tried saving the workbook and re-opening it
but the cell remains blank unless I switch to another
worksheet and back again
Again, I really appreciate your help!!!
Vince




.Hi Julie:
I'm afraid I am asking too much of you - it isn't your
task in life to train me in VB. I scan the book and it
seems to avoid simple tasks. I've tried Google for a
list of commands (for example: goto - or moveto, etc. -
so I can say 'if whatever, goto "a1")' and cannot find
such a list. If you point me in the right direction I'll
go there and study. Having started life as a bookkeeping
machine salesperson I am strong on applications and look
to programming as a list of simple commands to copy into
a program to get my job done.

Having said that, the code you gave me generated "Run-
time error '9': Subscript out of range." Here is the
code I entered:
Private Sub Workbook_Open()
If IsEmpty(Sheets("NewInput").Range("B2")) Then
Sheets("NewInput").Range("B2").Value = Format(Now, "dd
mmmm yyyy")
End If
End Sub

If you will help one more time with the above questions
I'll go it alone from that point. But will hope you can
solve the first three questions in my original posting
when you get the time. (I'll do the Google search you
recommended before or during the Labor Day weekend.)

Again, thank you very much.
Vince



Hi Vince

i'm happy to help so don't worry about asking questions ..

have you got a sheet in the workbook called "NewInput" as the code runs fine
when i copy & paste it into a new workbook with a sheet called NewInput (no
spaces) ...
is the code in the THISWORKBOOK object of the project explorer and not in a
module sheet
is the format stuff all on one line?

as to the other 3 questions i will get to them when i get a chance.

Looking forward to hearing from you.

Cheers
JulieD

-----Original Message-----

"VJ7777" wrote in
message
...

-----Original Message-----
Hi Vince

you can also put code against the THISWORKBOOK object
on
the workbook_open
event (double click on ThisWorkbook in the VBE window,
choose Workbook from
the lhs drop down & Open from the right),
you'll need to edit the code to:

If IsEmpty(Sheets("NewInput").Range("B2")) then
Sheets("NewInput").Range("B2").Value = Format
(Now, "dd mmmm yyyy")
End If

hope this helps
Cheers
JulieD


"VJ7777" wrote
in
message
...

-----Original Message-----
Hi Vince

to place the code in the workbook, right mouse
click
on
any sheet tab and
choose view code. This will display the VB
Editor ...
on the top left there
should be a little area with folders & sheets & the
name
of your workbook in
bold - if not, choose View / Project Explorer from
the
menu.

under the project with your workbook name in bold,
you
will see "NewInput"
and the rest of your sheets - double click on the
NewInput one, and a white
piece of paper should appear on the right hand
side of
the screen , up the
top are two drop down boxes, choose
worksheet
from the lhs one and Activate from the right
then the words

Private Sub Worksheet_Activate()
End Sub

should appear .. place my code between these two
lines.
Change the A1 to B2
You can "switch" between the code window & your
workbook
by using Alt & F1l
.... switch back, ensure B2 on the NewInput sheet
has
nothing in it, switch
to another sheet, come back to the NewInput sheet
the
date should be filled
in. Now you can test out both bits of code i gave
you
to decide which one
is the one you want.

(by the way, it is always a good idea to make a
backup
testing any code etc)

With regards to your other questions, i'm still
having a
look at them, but
i've got a few other things on at the moment and
havent'
really had time to
sit down & think about them properly. If you
haven't
done so already you
might like to do a search of google
(groups.google.com -
advanced search:
search string changing workbook names formulas ...
search groups
microsoft.public.excel* ), i would do it for you
and
see
if i come up with
anything but my internet explorer's not working at
the
moment

Hope this helps
Cheers
JulieD


wrote in
message
...

-----Original Message-----
Hi

can't help (yet) with the other questions but
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?

you can put code against each sheet (itself) in
the
vbe
window similar to
Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell
address
as
required
Range("A1").Value = Now
End If
End Sub

or

Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell
address
as
required
Range("A1").Value = Format(Now, "dd mmmm
yyyy")
End If
End Sub

Hope this helps
Cheers
JulieD



"VJ7777"
wrote
in
message
...
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.



Hi, Julie:
Thank you , very much for your response. I must
confess
complete lack of knowledge on how to start
implementing
your code. I have been in the computer industry
since
the beginning (about th
etimeofEniac.Itaught
myself how to read and modify Fortran code when I
was
product manager of a capital investment
evaluation
model
for Fortune 500 companies using Computer
Sciences'
time
sharing system. I taught myself how to program
in
Basic
when I had the first Apple computer and, for fun,
designed my own General Ledger system. On the
other
hand, I have pulled out the Visual Basic book
multiple
times and, as I did tonight, asked myself why I
should
punish myself when Microsoft's explanation never
seems
to
explain a place to begin.

If it isn't asking too much, could you please
tell
me
what steps to take to simply make your code work
in
cell "B2" of worksheet "New Input" of
workbook "aaCustomer." Maybe, if you do that, I
can
get
the concept of how to begin to use VB -
certainly I
have
never gotten a clue from the Microsoft VB book.

Your message seemed to indicate that you might
have
answers to my other questions - and I suspect VB
will
be
a major part of your answer. If so, I will need
to
know
how to intergrate them into Excel; your response
regarding to how to implement this will be very
necessary
to understanding your other solutions.
Thanks again,
Vince



.Julie, thanks a million. I'll try the google
search
later today. One quick question: The date appears
OK
after I switch to another worksheet and back again,
but,
suppose one doesn't go to another worksheet in
today's
data entry session; how does today's date get into
the
cell. I've tried saving the workbook and re-
opening it
but the cell remains blank unless I switch to
another
worksheet and back again
Again, I really appreciate your help!!!
Vince




.Hi Julie:
I'm afraid I am asking too much of you - it isn't your
task in life to train me in VB. I scan the book and it
seems to avoid simple tasks. I've tried Google for a
list of commands (for example: goto - or moveto, etc. -
so I can say 'if whatever, goto "a1")' and cannot find
such a list. If you point me in the right direction
I'll
go there and study. Having started life as a
bookkeeping
machine salesperson I am strong on applications and
look
to programming as a list of simple commands to copy
into
a program to get my job done.

Having said that, the code you gave me generated "Run-
time error '9': Subscript out of range." Here is the
code I entered:
Private Sub Workbook_Open()
If IsEmpty(Sheets("NewInput").Range("B2")) Then
Sheets("NewInput").Range("B2").Value = Format(Now, "dd
mmmm yyyy")
End If
End Sub

If you will help one more time with the above questions
I'll go it alone from that point. But will hope you
can
solve the first three questions in my original posting
when you get the time. (I'll do the Google search you
recommended before or during the Labor Day weekend.)

Again, thank you very much.
Vince



Hi Vince

i'm happy to help so don't worry about asking
questions ..

have you got a sheet in the workbook called "NewInput"
as the code runs fine
when i copy & paste it into a new workbook with a sheet
called NewInput (no
spaces) ...
is the code in the THISWORKBOOK object of the project
explorer and not in a
module sheet
is the format stuff all on one line?

as to the other 3 questions i will get to them when i
get a chance.

Looking forward to hearing from you.

Cheers
JulieD


.Hi Julie
Please forgive my stupidity. While shaving I realized my
worksheet is New_Input; not, NewInput. I made the change
and it worked fine.

Do you know a source for VB commands such as "goto"?

Thanks again.
Vince

wrote in message
...

-----Original Message-----

"VJ7777" wrote in
message
...

-----Original Message-----
Hi Vince

you can also put code against the THISWORKBOOK object
on
the workbook_open
event (double click on ThisWorkbook in the VBE window,
choose Workbook from
the lhs drop down & Open from the right),
you'll need to edit the code to:

If IsEmpty(Sheets("NewInput").Range("B2")) then
Sheets("NewInput").Range("B2").Value = Format
(Now, "dd mmmm yyyy")
End If

hope this helps
Cheers
JulieD


"VJ7777" wrote
in
message
...

-----Original Message-----
Hi Vince

to place the code in the workbook, right mouse
click
on
any sheet tab and
choose view code. This will display the VB
Editor ...
on the top left there
should be a little area with folders & sheets & the
name
of your workbook in
bold - if not, choose View / Project Explorer from
the
menu.

under the project with your workbook name in bold,
you
will see "NewInput"
and the rest of your sheets - double click on the
NewInput one, and a white
piece of paper should appear on the right hand
side of
the screen , up the
top are two drop down boxes, choose
worksheet
from the lhs one and Activate from the right
then the words

Private Sub Worksheet_Activate()
End Sub

should appear .. place my code between these two
lines.
Change the A1 to B2
You can "switch" between the code window & your
workbook
by using Alt & F1l
.... switch back, ensure B2 on the NewInput sheet
has
nothing in it, switch
to another sheet, come back to the NewInput sheet
the
date should be filled
in. Now you can test out both bits of code i gave
you
to decide which one
is the one you want.

(by the way, it is always a good idea to make a
backup
testing any code etc)

With regards to your other questions, i'm still
having a
look at them, but
i've got a few other things on at the moment and
havent'
really had time to
sit down & think about them properly. If you
haven't
done so already you
might like to do a search of google
(groups.google.com -
advanced search:
search string changing workbook names formulas ...
search groups
microsoft.public.excel* ), i would do it for you
and
see
if i come up with
anything but my internet explorer's not working at
the
moment

Hope this helps
Cheers
JulieD


wrote in
message
...

-----Original Message-----
Hi

can't help (yet) with the other questions but
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?

you can put code against each sheet (itself) in
the
vbe
window similar to
Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell
address
as
required
Range("A1").Value = Now
End If
End Sub

or

Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell
address
as
required
Range("A1").Value = Format(Now, "dd mmmm
yyyy")
End If
End Sub

Hope this helps
Cheers
JulieD



"VJ7777"
wrote
in
message
...
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.



Hi, Julie:
Thank you , very much for your response. I must
confess
complete lack of knowledge on how to start
implementing
your code. I have been in the computer industry
since
the beginning (about th
etimeofEniac.Itaught
myself how to read and modify Fortran code when I
was
product manager of a capital investment
evaluation
model
for Fortune 500 companies using Computer
Sciences'
time
sharing system. I taught myself how to program
in
Basic
when I had the first Apple computer and, for fun,
designed my own General Ledger system. On the
other
hand, I have pulled out the Visual Basic book
multiple
times and, as I did tonight, asked myself why I
should
punish myself when Microsoft's explanation never
seems
to
explain a place to begin.

If it isn't asking too much, could you please
tell
me
what steps to take to simply make your code work
in
cell "B2" of worksheet "New Input" of
workbook "aaCustomer." Maybe, if you do that, I
can
get
the concept of how to begin to use VB -
certainly I
have
never gotten a clue from the Microsoft VB book.

Your message seemed to indicate that you might
have
answers to my other questions - and I suspect VB
will
be
a major part of your answer. If so, I will need
to
know
how to intergrate them into Excel; your response
regarding to how to implement this will be very
necessary
to understanding your other solutions.
Thanks again,
Vince



.Julie, thanks a million. I'll try the google
search
later today. One quick question: The date appears
OK
after I switch to another worksheet and back again,
but,
suppose one doesn't go to another worksheet in
today's
data entry session; how does today's date get into
the
cell. I've tried saving the workbook and re-
opening it
but the cell remains blank unless I switch to
another
worksheet and back again
Again, I really appreciate your help!!!
Vince




.Hi Julie:
I'm afraid I am asking too much of you - it isn't your
task in life to train me in VB. I scan the book and it
seems to avoid simple tasks. I've tried Google for a
list of commands (for example: goto - or moveto, etc. -
so I can say 'if whatever, goto "a1")' and cannot find
such a list. If you point me in the right direction
I'll
go there and study. Having started life as a
bookkeeping
machine salesperson I am strong on applications and
look
to programming as a list of simple commands to copy
into
a program to get my job done.

Having said that, the code you gave me generated "Run-
time error '9': Subscript out of range." Here is the
code I entered:
Private Sub Workbook_Open()
If IsEmpty(Sheets("NewInput").Range("B2")) Then
Sheets("NewInput").Range("B2").Value = Format(Now, "dd
mmmm yyyy")
End If
End Sub

If you will help one more time with the above questions
I'll go it alone from that point. But will hope you
can
solve the first three questions in my original posting
when you get the time. (I'll do the Google search you
recommended before or during the Labor Day weekend.)

Again, thank you very much.
Vince



Hi Vince

i'm happy to help so don't worry about asking
questions ..

have you got a sheet in the workbook called "NewInput"
as the code runs fine
when i copy & paste it into a new workbook with a sheet
called NewInput (no
spaces) ...
is the code in the THISWORKBOOK object of the project
explorer and not in a
module sheet
is the format stuff all on one line?

as to the other 3 questions i will get to them when i
get a chance.

Looking forward to hearing from you.

Cheers
JulieD


.Hi Julie
Please forgive my stupidity. While shaving I realized my
worksheet is New_Input; not, NewInput. I made the change
and it worked fine.

Do you know a source for VB commands such as "goto"?

Thanks again.
Vince


Hi Vince

glad its working .. basically the way you need to approach VBA is to ask
yourself what object am i looking at and what do i want to do with it, so if
it's a cell (e.g. A1), then you need to know that cells are part of the
range object and that range objects have a select method, therefore to
select a cell you can use
Range("A1").select

however, if you want to select a cell on another sheet to need to make the
sheet active & then select the cell
Sheets("Sheet1").activate
Range("A1").select

however, you can populate a cell without selecting it
Sheets("Sheet1").Range("A1").value = 5
will put the number 5 into cell A1 on Sheet1

the best place to find all this out is using the Object Browser in VBA (view
/ object browser) or type "microsoft excel object model" into Help in the
VBE .. this will give you all the objects you can then click on them to find
out their properties & methods.

Hope this helps
Cheers
JulieD