Multi sheet population

[jeffwest1]

I have used VBA in Excel, but not for a while, so excuse the rather long winded question.

I have a crystal report that quite happly will export out the data I need into excel, this data contains a single entry for a number of users, each with a unique number.

I want to add the data into individual spreadsheets for those people, so want to have a macro to examine the export data, and populate a series of spreadsheets from that data based on a unique identifer.

Although for January I may have 75 people, Feb I will have 100, while these are in identifer number order becase there may be gaps i.e.
Jan 1,2,3,5,7,9
Feb 1,2,4,5.6.7.9

I cannot just hardcode links from output spreadsheet to individual spreadsheet, as they may not be in the same place each time, anyone know or give me a clue as to how VB code can do a search and populate accordingly.


--------------------------------
Luke: But tell me why I can't...
Yoda: No, no, there is no why. Nothing more will I teach you today. Clear your mind of questions.

 

[SkipVought]

Hi,

Forst get your data in tabluar format as posted in the VBScript forum.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[jeffwest1]

Right, i have my export

Month ID NEET
Feb 1 56
Feb 8 23
Feb 11 35
Feb 14 8
Feb 24 9
Feb 28 16
Feb 47 18

This I now need to look at populating the data from here into the individual spreadsheets?

--------------------------------
Luke: But tell me why I can't...
Yoda: No, no, there is no why. Nothing more will I teach you today. Clear your mind of questions.

 

[SkipVought]

You could use a PivotTable with the UID in a PAGE field.

You could use MS Query with a parameter for the UID. faq68-5829.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[jeffwest1]

Thats all well and good using a pivot table, however, the individual spreadsheets are already laid out, not of my doing i just have to populate them.

Also, as stated in the other thread, i have upto 6 different things i need to add onto each person spreadsheet, this x 100+ staff, i would really prefer to be doing this with a single click run a macro rather than copy and paste.

I have looked into the MS Query stuff, and am thinking of looking at this for a short term solution, but would prefer something a bit more long term.

--------------------------------
Luke: But tell me why I can't...
Yoda: No, no, there is no why. Nothing more will I teach you today. Clear your mind of questions.

 

[SkipVought]

"individual spreadsheets are already laid out, not of my doing i just have to populate them."

Well then, don't you think that we need to know what the target looks like before offering any cogent solution?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[jeffwest1]

Ok, I may not have been as helpful as i could have been

Jan Feb Mar
Target Actual Target Actual Target Actual
Data Set 1
Data Set 2
Data Set 3
Data Set 4
Data Set 5
Data Set 6

The target will be pre set, i need the figures for each data set to go into the Actual column for each staff member for each month.

Crystal to export each dataset as a seperate spreadsheet, hope this helps you visualise my problem :-(

--------------------------------
Luke: But tell me why I can't...
Yoda: No, no, there is no why. Nothing more will I teach you today. Clear your mind of questions.

 

[SkipVought]

You have an apparent potential problem: months that are only strings, rather than DATES. I almost never use month strings or year numbers: I almost always use REAL DATES, formatted to DISPLAY month or year.

Potential, because I do not know how your application works, but you will never be able to colate properly on month, or do other more complex analysis related to your "dates".

So how do these two relate? What are these Data Sets?

Using the example you posted, what should the result be?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[jeffwest1]

The target spreadsheets are a rolling 12 months, it only needs to be the months rather than actual dates, i don't need it to do anything complex, just move the data from the combined staff spreadsheet, to the individual one for each member of staff.

The month is dervied from when the output via crystal is done, Jan, Feb, March etc the data sets being produced at the end of the month that it relates to.

The data sets produced are to do with targets each person has, i have report into our database that calculates how many clients the staff member has of 6 distinct groups of people on caseload.

Other work is done to do with aggregated data into teams and office, but this now needs to be at individual staff member level.

As i said, the layout is not of my doing, once i have the individual ones done, these will then have to feed into Team/Area/Company ones, but that is from a static list, 4 teams in each area, 4 areas in the company, but the staff list in the 6 client groups will not be the same, so will not be a static list, otherwise it would be easy paste links from a static file to a static file.

--------------------------------
Luke: But tell me why I can't...
Yoda: No, no, there is no why. Nothing more will I teach you today. Clear your mind of questions.

 

[SkipVought]

Using the example you posted, what should the result be?

PLEASE!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[jeffwest1]

Crystal reports dervied output

Month ID Data Set 1
Feb 1 55
Feb 8 23
Feb 11 35
Feb 14 8
Feb 24 9
Feb 28 16
Feb 47 18

Macro run against output file, populating individual Staff Member spreadsheet

STAFF MEMBER 1

Jan Feb Mar
Target Actual Target Actual Target Actual
Data Set 1 59 55 61
Data Set 2 22 25 18
Data Set 3 45 45 46
Data Set 4 26 10 15
Data Set 5 0 0 1
Data Set 6 12 8 10


--------------------------------
Luke: But tell me why I can't...
Yoda: No, no, there is no why. Nothing more will I teach you today. Clear your mind of questions.

 

[SkipVought]

Help yourself out!

The ONLY value in your example that I can relate is the 55
[tt]
Jan Feb Mar
Target Actual Target Actual Target Actual
Data Set 1 59 55 61
Data Set 2 22 25 18
Data Set 3 45 45 46
Data Set 4 26 10 15
Data Set 5 0 0 1
Data Set 6 12 8 10
[/tt]
Where did the other values come from?

Please be clear, concise and complete in your examples and answers!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[jeffwest1]

Ok, you asked me for how the data should look, i have posted how the data will look having populated all the figures i need populating from the spresheets created monthly.

I have told you that my report exports out each of the 6 data sets listing a unique number for each staff member, and by that the figure for the number of clients in that dataset on caseload

I told you I have 6 data sets, i have populated my example with 6 data sets of data.

I told you this is done monthly, so i have populated 3 months worth of data, to show you what i am trying to acheive. although i have only shown you a partial data set for one month.

Sorry for getting funny, and reading some of your past posts i can see you aren't stupid, so wondering if it is just me you are getting funny with.



--------------------------------
Luke: But tell me why I can't...
Yoda: No, no, there is no why. Nothing more will I teach you today. Clear your mind of questions.

 

[SkipVought]

aaaaaahhhhh.

You changed NEET to Data Set 1!!!

Your source data is AGAIN in need of reformatting, it seems...
[tt]
Month ID Data Set DS_Val
Feb 1 1 55
Feb 8 1 23
Feb 11 1 35
Feb 14 1 8
Feb 24 1 9
Feb 28 1 16
Feb 47 1 18
[/tt]
This ONE TABLE must hold ALL the information that will populate your report.


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Jeff,

I cannot provide a cogent solution if you do not provide consistent relatable data.

You notice that your original table had NEET as a column heading, that you CHANGED to Data Set 1. I was expecting something consistent. I did not notice that change.

Give me consistent, clear examples and discussion, and I, or others, will get you one or more solutions or tips to get you going in a good direction.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[jeffwest1]

I used NEET in the first example as that was the original title of the column, I changed it to Data Set 1 to try and show you that I have six data sets to populate for each staff member, and the titles of these are meaningless to anyone outside of the company.

Just assume Data Set 1 for now.

Data Set 1 Crystal reports export, this will be exported to a static place on the server, with all the individual ones in the same folder.

Exported Spreadsheet example
Month ID Value
Feb 1 55
Feb 2 58
Feb 3 6
. . .
. . .
. . .

Each month that the export is done, adds into the month column the month it is run for i.e. Jan, Feb, March etc, the id (this is the staff number), which will relate to the individual staff member spreadsheet 1,2,3,4 etc, and a calculated value for that staff member in that exported month.

The individual spredsheet is a rolling twelve months (at this time, although I may need to create a tab for each year, or overwrite the data at the start of next year, this as yet is not clear) starting in April (although i need to be testing this now)from the export I will populate each month with the values from that months export.

What I could do with, is a headstart on code to read the combined exported out spreadsheet, find each staff id within that file, find the value associated with that staff id for that month, then populate the correct staff spreadseet for the correct month with the value, taking it that the export will not always have the id's listed 1,2,3,4,5.....etc but may be 1,3,6,9....etc I may instead use actual names of the staff members as the id, so that it relates to the named sheet i.e

Exported Spreadsheet example
Month ID Value
Feb John Smith 55
Feb James Smith 58
Feb David Smith 6
. . .
. . .
. . .

Spreadsheet called John Smith.xls

Ideally if this could be run against the combined spreadsheet, then that would save me having to open 100+ spreadsheets to do them individually.



--------------------------------
Luke: But tell me why I can't...
Yoda: No, no, there is no why. Nothing more will I teach you today. Clear your mind of questions.

 

[SkipVought]

Having six indivisual data sets make the job more difficult.

If thats the best you can get from CR, then I'd consolidate the data, once a month, in a format similar to the one I previously posted, with a column for the Data Set # and another column for the Data Set Value.

Then the solution is a simple as this.

1. the Staff ID goes in A1 on each sheet.

2. the formula in C3 on each sheet...
[tt]
=SUMPRODUCT((ID=$A$1)*(Mon=B$1)*(Data_Set=$A3)*(DS_Value))
[/tt]
assuming that your source data looks like this...
[tt]
Mon ID Data_Set DS_Value
Feb-09 1 Data Set 1 56
Feb-09 8 Data Set 1 23
Feb-09 11 Data Set 1 35
Feb-09 14 Data Set 1 8
Feb-09 24 Data Set 1 9
Feb-09 28 Data Set 1 16
Feb-09 47 Data Set 1 18
[/tt]


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Man, you seem to have a real bear of a deal here. And it all backwards, and consequently going to be labor intensive managing all this workbooks.

Where do the Target values come from? IMHO, all that data ought to be in ONE table, and using REAL DATES, so that your year wrapping would not be a problem...
[tt]
MonDate ID Data_Set Target Actual
[/tt]
Generating 100+ reports from that source would be a piece of cake.



Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[jeffwest1]

I'm not calculating the target, that will be manually added by the Team managers, again i just used it as an example of the layout.

--------------------------------
Luke: But tell me why I can't...
Yoda: No, no, there is no why. Nothing more will I teach you today. Clear your mind of questions.

 

[jeffwest1]

Going to be really dumb here.

The code you say goes into C3 on each sheet, but this code doesn't seem to have anything that reads from my combined workbook/spraedsheet, and finds the approprite staff named workbook/spreadsheet, finds the appropriate month in that staff named sheet and fills it in accordingly.

or are you saying that the code actually needs to read

=SUMPRODUCT((ID=[StaffNamedeSheet.xls]Sheet1!$A$1)*(Mon=[ExportSheetName.xls]Sheet1!B$1)*(Data_Set=[ExportSheetName.xls].Sheet1!$A3)*([ExportSheetName.xls]Sheet1!DS_Value))

Or have i got it completely wrong




--------------------------------
Luke: But tell me why I can't...
Yoda: No, no, there is no why. Nothing more will I teach you today. Clear your mind of questions.