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" <Anne@discussions.microsoft.com> wrote in message 
news:2EA72DF9-2627-4ECD-A4EF-12BBCB39605C@microsoft.com...
> 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" <Anne@discussions.microsoft.com> wrote in message 
> news:2EA72DF9-2627-4ECD-A4EF-12BBCB39605C@microsoft.com...
> > 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" <Anne@discussions.microsoft.com> wrote in message 
news:63B7EAD2-F24E-4B5C-95E7-297009F90733@microsoft.com...
> 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" <Anne@discussions.microsoft.com> wrote in message
>> news:2EA72DF9-2627-4ECD-A4EF-12BBCB39605C@microsoft.com...
>> > 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!
>> >
>>
>>
>> .
>>