2 worksheets--3 questions

Hello! I have a workbook that tracks patients (mothers) and contacts (mom's
family members). The mother worksheet is named MAT_INF and the contacts one
is named CONTACTS.

1. In MAT_INF, I have a field named CASE_NUMBER. How can I tell this field
to automatically start out with a value like "2009-"? An example case number
would look like 2009-001 or 2010-099.

2. Once CASE_NUMBER, LAST_NAME_MOM and FIRST_NAME_MOM have been populated by
the user in MAT_INF, how can I automate adding those values to the CONTACT
sheet, where the corresponding fields are named CASE_NUMBER, CASE_LAST_NAME
and CASE_FIRST_NAME?

3. Once CASE_NUMBER in the CONTACT sheet has been brought over from the
MAT_INF sheet, how can I add a sequential number to it? For example, for
CASE_NUMBER 2009-001 from the MAT_INF sheet, with LAST_NAME_MOM equal to Doe
and FIRST_NAME_MOM equal to Jane, how would I get the CONTACT information to
look like CASE_NUMBER 2009-001-01, CASE_LAST_NAME equal to Doe and
CASE_FIRST_NAME equal to Jane?

Each CASE_NUMBER from the MAT_INF sheet (Jane Doe, 2009-001) may have 1 or
more contacts (other family members) in the CONTACTS sheet. So we might have
an entry in the CONTACT sheet like 2009-001-01, CASE_LAST_NAME = Doe,
CASE_FIRST_NAME = Jane, CONTACT_LAST_NAME = Doe, CONTACT_FIRST_NAME = Zippy.

Any help would be greatly appreciated!!! Thanks!

Anne,

There seems to be unnecessary replication of data here.

I think I would just have one sheet with all of the data stored there, like
a database, and have other sheets giving the particular views, say Patients
and Contacts, which are just formula linking into the database, or even a
simple VBA report (although I must admit I was not clear on points 3 on).

--

HTH

Bob

"Anne" wrote in message
...
Hello! I have a workbook that tracks patients (mothers) and contacts
(mom's
family members). The mother worksheet is named MAT_INF and the contacts
one
is named CONTACTS.

1. In MAT_INF, I have a field named CASE_NUMBER. How can I tell this field
to automatically start out with a value like "2009-"? An example case
number
would look like 2009-001 or 2010-099.

2. Once CASE_NUMBER, LAST_NAME_MOM and FIRST_NAME_MOM have been populated
by
the user in MAT_INF, how can I automate adding those values to the CONTACT
sheet, where the corresponding fields are named CASE_NUMBER,
CASE_LAST_NAME
and CASE_FIRST_NAME?

3. Once CASE_NUMBER in the CONTACT sheet has been brought over from the
MAT_INF sheet, how can I add a sequential number to it? For example, for
CASE_NUMBER 2009-001 from the MAT_INF sheet, with LAST_NAME_MOM equal to
Doe
and FIRST_NAME_MOM equal to Jane, how would I get the CONTACT information
to
look like CASE_NUMBER 2009-001-01, CASE_LAST_NAME equal to Doe and
CASE_FIRST_NAME equal to Jane?

Each CASE_NUMBER from the MAT_INF sheet (Jane Doe, 2009-001) may have 1 or
more contacts (other family members) in the CONTACTS sheet. So we might
have
an entry in the CONTACT sheet like 2009-001-01, CASE_LAST_NAME = Doe,
CASE_FIRST_NAME = Jane, CONTACT_LAST_NAME = Doe, CONTACT_FIRST_NAME =
Zippy.

Any help would be greatly appreciated!!! Thanks!

thanks, Bob. I'm just working with a legacy system that I inherited. I can
check to see if it's permissable to have it all on one sheet--that would be
more convenient in many ways--but there may be some reason we have the
mother's info on one sheet separate from the contact's info on the second
sheet.

Thanks!

"Bob Phillips" wrote:

Anne,

There seems to be unnecessary replication of data here.

I think I would just have one sheet with all of the data stored there, like
a database, and have other sheets giving the particular views, say Patients
and Contacts, which are just formula linking into the database, or even a
simple VBA report (although I must admit I was not clear on points 3 on).

--

HTH

Bob

"Anne" wrote in message
...
Hello! I have a workbook that tracks patients (mothers) and contacts
(mom's
family members). The mother worksheet is named MAT_INF and the contacts
one
is named CONTACTS.

1. In MAT_INF, I have a field named CASE_NUMBER. How can I tell this field
to automatically start out with a value like "2009-"? An example case
number
would look like 2009-001 or 2010-099.

2. Once CASE_NUMBER, LAST_NAME_MOM and FIRST_NAME_MOM have been populated
by
the user in MAT_INF, how can I automate adding those values to the CONTACT
sheet, where the corresponding fields are named CASE_NUMBER,
CASE_LAST_NAME
and CASE_FIRST_NAME?

3. Once CASE_NUMBER in the CONTACT sheet has been brought over from the
MAT_INF sheet, how can I add a sequential number to it? For example, for
CASE_NUMBER 2009-001 from the MAT_INF sheet, with LAST_NAME_MOM equal to
Doe
and FIRST_NAME_MOM equal to Jane, how would I get the CONTACT information
to
look like CASE_NUMBER 2009-001-01, CASE_LAST_NAME equal to Doe and
CASE_FIRST_NAME equal to Jane?

Each CASE_NUMBER from the MAT_INF sheet (Jane Doe, 2009-001) may have 1 or
more contacts (other family members) in the CONTACTS sheet. So we might
have
an entry in the CONTACT sheet like 2009-001-01, CASE_LAST_NAME = Doe,
CASE_FIRST_NAME = Jane, CONTACT_LAST_NAME = Doe, CONTACT_FIRST_NAME =
Zippy.

Any help would be greatly appreciated!!! Thanks!



.

Anne,

With a bit of work, you still have the separate reports. It is better to
have the data consolidated, it is so much easier to get any view you want
then.

--

HTH

Bob

"Anne" wrote in message
...
thanks, Bob. I'm just working with a legacy system that I inherited. I can
check to see if it's permissable to have it all on one sheet--that would
be
more convenient in many ways--but there may be some reason we have the
mother's info on one sheet separate from the contact's info on the second
sheet.

Thanks!

"Bob Phillips" wrote:

Anne,

There seems to be unnecessary replication of data here.

I think I would just have one sheet with all of the data stored there,
like
a database, and have other sheets giving the particular views, say
Patients
and Contacts, which are just formula linking into the database, or even a
simple VBA report (although I must admit I was not clear on points 3 on).

--

HTH

Bob

"Anne" wrote in message
...
Hello! I have a workbook that tracks patients (mothers) and contacts
(mom's
family members). The mother worksheet is named MAT_INF and the contacts
one
is named CONTACTS.

1. In MAT_INF, I have a field named CASE_NUMBER. How can I tell this
field
to automatically start out with a value like "2009-"? An example case
number
would look like 2009-001 or 2010-099.

2. Once CASE_NUMBER, LAST_NAME_MOM and FIRST_NAME_MOM have been
populated
by
the user in MAT_INF, how can I automate adding those values to the
CONTACT
sheet, where the corresponding fields are named CASE_NUMBER,
CASE_LAST_NAME
and CASE_FIRST_NAME?

3. Once CASE_NUMBER in the CONTACT sheet has been brought over from the
MAT_INF sheet, how can I add a sequential number to it? For example,
for
CASE_NUMBER 2009-001 from the MAT_INF sheet, with LAST_NAME_MOM equal
to
Doe
and FIRST_NAME_MOM equal to Jane, how would I get the CONTACT
information
to
look like CASE_NUMBER 2009-001-01, CASE_LAST_NAME equal to Doe and
CASE_FIRST_NAME equal to Jane?

Each CASE_NUMBER from the MAT_INF sheet (Jane Doe, 2009-001) may have 1
or
more contacts (other family members) in the CONTACTS sheet. So we might
have
an entry in the CONTACT sheet like 2009-001-01, CASE_LAST_NAME = Doe,
CASE_FIRST_NAME = Jane, CONTACT_LAST_NAME = Doe, CONTACT_FIRST_NAME =
Zippy.

Any help would be greatly appreciated!!! Thanks!



.