2 worksheets--3 questions

  • Thread starter Thread starter Anne
  • Start date Start date
A

Anne

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).
 
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!
 
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.
 
Back
Top