From One Worksheet to Many 1

[shelby55]

Hello

I am using Excel 2003.

Skip was very helpful in allowing me to take many worksheets that had the same data in the same cell per worksheet and by mapping out the fields allow me to extract all the data onto one worksheet (thanks again Skip!!).

Now I would like to do the reverse: take the one worksheet and create many worksheets.

Each line of data in the "one" worksheet is one hospital visit. I would like one worksheet per hospital visit. I'll want each data element to be in the same cell(s) on the new worksheet but it won't be in the same format as an entire row.

For instance:
A B C
1 Chart Number 123456
2 admit date 4/10/2010

Thanks very much.

 

[SkipVought]

Hi,

Each sheet/table/chart etc, has an overhead of maintenance and storage.

That being said, why not use a control to display only the data you want on one sheet, rather than umpteen sheets?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

Because each coder wants a sheet per hospital visit or "abstract" that can be printed on its own.

Is this possible?

 

[SkipVought]

There are any number of ways to spawn multiple sheets. However, your answer does not in ANY way preclude my solution.

1) When the user makes the selection, they see ONLY the data for that selection, just as they would SEARCHING for their sheet tab.

2) When the user makes the selection, then can PRINT the sheet.

Okay, the 'other' spawning methods:

A. In a loop, filter on each visit, ADD a sheet, copy the visible data and paste to the new sheet.

B. In a loop, Add a sheet, insert a PivotTable to return the associated visit.

C. In a loop, ADD a sheet, insert a QueryTable to return the associated visit.

BTW, in the case of B, the PAGE field CAN be used to select a visit. HUH! In the case of C, a Data > Validation cell CAN be used to select a visit and trigger a 'parameter query.' HUH!

It is so much more professional to present the data in a compact manner, than to FORCE the user to SEARCH thru a voluminous workbook to FIND their data; one sheet in umpteen!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

To explain the process to you: I receive abstract data in a file we will now call "original data". I want to plunk it into the various worksheets as indicated. Then the coder will go into each worksheet as he/she reviews each abstract/patient chart and comes up with "new data" (potentially). Then I extract all the new data as per the method you provided originally.

That is why this isn't an exercise in data presentation, it is data collection. And yes I know it would be better to have in an Access database but that isn't an option right now.

Good point about searching...can the tab title be manipulated to show a data element like account number?

Thanks.

 

[SkipVought]

assuming that the acct nbr is in A1 and assuming that the format does not violate sheet naming rules...

with worksheets.add
  .cells(1,1).value = MyAcctNbr
  .name = .cells(1,1).value
end with

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Thanks Skip.

Is there any other post or reference you can point me to in order to assist with solution A?

The data elements will need to have descriptive labels - is there a way to take a template and have the data per abstract drop into that template creating a worksheet for each?

Thanks.

 

[SkipVought]

First you will need a UNIQUE LIST that represents the visits/sheets you need to generate. I would suggest that that data is in your source data. so it would simply be a QUERY on a new sheet of DISTINCT values for that field...

Select Distinct [Some Field Name]
From [Your Source Sheet$]

That data will be the source for your LOOP.

Is your 'template' for your spawned sheets simply...
[tt]
Chart Number
admit date
[/tt]
?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Yes you are correct about the what the template is.

I understand your comments about the data worksheet so that is good. Thanks.

 

[SkipVought]

dim r as range, wsList as worksheet, wsTmpl as worksheet, rT as range, lRow as long

set wsList = sheets("YourUniqueList")

set wsTmpl = sheets("YourTemplate")

for each r in wsList.range(wsList.cells(2, 1), wsList.cells(2, 1).end(xldown))
   with worksheets.Add
      .name = r.value
      lRow = 1
      for each rT in wsTmpl.Range(wsTmpl.cells(1, 1), wsTmpl.cells(1, 1).end(xldown))
         
         with .cells(lrow, 1)
            .value = rt.value
            select case lrow
               case 1  'chart nbr
                  .offset(0,1).value = r.value
               case 2  'admit date
                  .offset(0,1).value = AdmitDate 'or whatever
            end select
            lrow = lrow + 1
         end with
      next
   end with
next

set wsList = nothing
set wsTmpl = nothing

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Thanks Skip!

Another question: when indicating the source of chart number, admit date etc. am I using the column head name as the reference?

So if in the raw data Column F is admit date with the header of admdate then admdate is what I would use?

Thanks.

 

[SkipVought]

Are you using Named Ranges? I almost ALWAYS do.

Assuming that you are, AND that the chart number header is chartnum, the admit date formula is...
[tt]
=INDEX(admdate,MATCH(YourCharNumValue,chartnum,0),1)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

I figured I would need to use named ranges and you just confirmed it.

Is it possible to transpose items and use them in the code? Using diagnoses as an example, there is a placeholder for 25 diagnoses because that is the maximum that could exist in the data though not all patient visits will have 25 diagnoses.

So if the named range for diagnoses was L2 to AJ 500 and the location for those 25 diagnoses on the template was C28 to C52, is it possible to write code to populate the template that way? Or do I need to create a named range for each column of data?

Thanks.

 

[SkipVought]

Your data is not normalized, and hence your dilema!

How do you specify a particular diagnosis for a particular visit?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

All patients could have a different diagnosis(es) like pneumonia, stroke, cardiac arrest to describe why there were in hospital and coding staff in Health Records assign values using ICD nomenclature for each diagnosis like J14, I64 etc.

It's not in error to not have 25 diagnoses but the abstract specs allow for 25 in case an abstract warrants it. So because not all abstracts will have the same number of diagnoses, I need to have a space for all 25 if I'm going to roll the new codes up to analyze.....which is the code you gave me from the first of this post.

 

[SkipVought]

You do not seem to understand, that the way that you and the hospital staff likes to VIEW and MANAGE the disnoses, is DEFINITELY NOT the way that the data ought to be organized and stored.

No database person would store the data as you have it displayed in your source sheet. If your data were stored as it ought to be stored, then looking up any patient's diagnoses would be much simpler.

Be as it may, it can be done using your non-normalized 'user view' but it will be a bit more involved lookup.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Thanks. So where would I find out how to do as you suggested with the "user view"?

 

[SkipVought]

I'd use Named Ranges and then use the INDIRECT function in my lookup.
[tt]
=INDEX(INDIRECT(Z1),MATCH(YourCharNumValue,chartnum,0),1)
[/tt]
where Z1 contains a diagnosis heading value.

CAVEAT: named ranges, using the values in the headers, changes SPACE to UNDERSCORE and preceeds leading numbers with an UNDERSCORE.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Thanks Skip, I'll give this a go!