Many Worksheets to One 1

[shelby55]

(originally posted in another forum....Skip told me to post here)

Hello

I'm using Excel 2003.

I am in receipt of an Excel file where each worksheet is one patient's data (50 worksheets). The data per worksheet isn't across one row i.e. CMG = C6, Sex = C15 etc. but it is in the same location per worksheet so all worksheets have CMG at the location of C6.

What I want to do is have a single worksheet for all the data with each chart having all their data elements in a row.

To answer Skip's questions:

What CELLS map to what columns?
The data isn't all in one column for instance C contains most but there are also headings in "C" and also data included in D. Actually this step will be after I've figured out a solution but C4 would be A2 in the master yet to be created, C5 would be B2, C6 would be C2. But that is for the first record, for the next worksheet it will be A3, B3, and C3.

Is each sheet identically structured?
All worksheets are identical in terms of where the data is located on the worksheet - sex is always C15, CMG is always C6.

What is master sheet named?
There is not master worksheet...that is something I want to create to combine all the data.

Is this possible? Thanks.




What is master sheet named?

 

[SkipVought]

You are mapping data from sheets to a master, YES?

Just go ahead and use the modifications I posted last.

Skip,

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

 

[shelby55]

Hi

Now it doesn't like

For Each r In [Source]

As I mentioned, right now there are no named ranges because I can't find out what you want to be named in the range or what the names are.....or if the third column now needs to be a named range.

Thanks.

 

[shelby55]

Figured it out....named ranges for column A and B and it works like a charm!! Thanks so much Skip!!

 

[SkipVought]

In the Mapping sheet...
[tt]
Source
C3 Chartno =MATCH(B2,Master!$1:$1,0)
C4 AcctNo
C5 AdmDate
C6 SepDate
[/tt]
with the formual copied down & calculated.

I suppose that the ONLY important NAMED RANGE on this sheet is Source.

there is no NAMED RANGE required on any other sheet.



Skip,

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

 

[shelby55]

Hi

Sorry but I have another question: can this method also format the fields being copied? For instance, some of the fields that are formatted as text on the worksheets (but are numbers i.e. 07) are coming across as numbers to the master sheet and I don't want that.

Thanks!

 

[SkipVought]

Would that not be a ONE TIME thing? Could you not format any column as desired?

Conversly, this statement could be changed to a copy 'n' paste...

[s]
wsMSTR.Cells(lRow, r.Offset(0, 2).Value) = .Range(r.Value)
[/s]
.Range(r.Value).copy wsMSTR.Cells(lRow, r.Offset(0, 2).Value)

Skip,

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

 

[shelby55]

Hi

One more question: what if the cell being referenced is a cell reference? For instance, the cell for original diagnosis occurrence #1 is located in cell Q43 but the Q43 value is =D43. The only time this would be different is if the code was changed and then it would be whatever was entered in that cell. Is there any way to format the mapping reference to indicate if statements to use D43 unless D43<>Q43 and then use Q43?

Thanks.

 

[SkipVought]

[tt]
=IF(D43=Q43,D43,Q43)
[/tt]


Skip,

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

 

[SkipVought]

But WHY, using your stated logic, would you not simply reference Q43, regardless of the value of D43?
[tt]
=Q43
[/tt]


Skip,

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

 

[shelby55]

Hi Skip

Because the result of using just Q43 (which is =D43) is showing up as #REF! or I wouldn't worry about it. So any field that is mapped as another cell reference isn't copying over correctly..does that make sense?

 

[SkipVought]

What is in D43? Trace it ALL THE WAY BACK.

Skip,

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

 

[shelby55]

Hi Skip

The issue was if I changed the code to copy:

.Range(r.Value).copy wsMSTR.Cells(lRow, r.Offset(0, 2).Value)

so it makes sense that it would be a problem, I changed back to the other and it's fine.

I switched to the copy version so that date/time fields would come across correctly. Formatting the columnns isn't working....

Thanks for your help, Skip.