Create merge from list into individual speadsheets

[MonkeyNut]

Hi there,

I have a Student List.
I have a Standard transcripts template.
How can I get certain fields such as Student name, i.d. etc. into relevant fields...is there a macro or script I can use?

Scenario.
One template
One student list
when the template inserts the relevant details from the list it should generate a new worksheet from that template with the first students details. Then consecutively create new worksheets for each subsequent student on the list, with their details.

I hope this makes any sense to anybuddy.
Will upload the list and the template.
The fields I need completed are in the National worksheet and highlighted in yellow.

Hope some brilliant genius out there can help me.

Oh p.s. had to upload as a pdf. Seems Chandoo cannot load Office 2013 files?
Meantime have a grand day
♪♫♪

 

[vletm]

Could You try to upload that file with better luck again?
You need the 'student list', 'template'.
I hope that You really don't want to have 30 more sheets! No need!
You need 'just' those two sheets.

 

[MonkeyNut]

Right. Google Chrome has an issue. Will report it. Meantime from IE here it is. Thanks vletm. Hope you can help.

 

[MonkeyNut]

Also. I am in South Africa so apologise for delayed replies. and again apologies for thinking it might be Chandoo problem for not being able to upload from Chrome.

 

[vletm]

There are no problems ... sometime challenges.
I did one sample for You.
1) Select any row from 'Student List'
2) You'll see 'National'. Filled with previous time left values.
I tested to save at least some values ...
You know better which values should save, okay?
Saving is 'automatic'.
3) There would be good to protect 'National'-sheet.
... Ideas ... questions? for next version...

 

[MonkeyNut]

vletm that is flipping brilliant. I am awed. I think though my problem is that each one should generate a sheet of their own. For management purposes. Each class has say 30 students right? they want to see the spreadsheet for each class in the workbook. Is that a task? or how should I approach them to this new way?

 

[MonkeyNut]

Or mabe my question should refer to the printing of this? How do i Print each one individually?

 

[vletm]

Okay ...

Do all classes has same 'National'-sheet layout?
If same layout then there should be also 'class code' in 'student list'
If different layout then every class own file.

Do You want to Print students 'National'-sheets
a) manually one-by-one
b) mark which students want to print out or all ( or class[es] )?

Q: Did I saved correct values for next time to view?

ps. Samuel Elliot's Student Number ...?

 

[vletm]

2nd version with almost printing possibility ...
Ideas .. Questions?

 

[MonkeyNut]

Good morning vletm. I have uploaded sample with one sheet recreated. The idea is to populate the National Format and layout into new sheets with the students first name. thereby having several worksheets in the workbook.

 

[vletm]

Good morning.
Two same kind of layouts ... why?
If You change something on those sheet and click back to list
next name ... back to previous name then there should be always
Your updated informations ( only date will be like today).
Anyway You can select which one You want to see.
[Done/Edit] let You modify informations.
[Print] ... sample how to select which and how to print

 

[MonkeyNut]

I am uploading the original again. Please note that in the second section of the National sheet, The Unit Standards - i.e. US ##### need to be there at all times. All columns need to be shown including Still to be taught. I have highlighted the column that keeps changing to "Still to be taught". I am delighted, and thank you again for your effort. I do not see your name listed in the donations area?

 

[MonkeyNut]

Good morning.
Two same kind of layouts ... why?
If You change something on those sheet and click back to list
next name ... back to previous name then there should be always
Your updated informations ( only date will be like today).
Anyway You can select which one You want to see.
[Done/Edit] let You modify informations.
[Print] ... sample how to select which and how to print

No you are right. This is great. Except the second half of the National layout changes. It should not read still to be taught. That column has Unit Standard numbers as per my first upload?
On the National sheet you have a click to go to student list? how does that work?

 

[vletm]

Maybe that You file will come with next time?
Mark with one color those which should save (each sheet).
So far this will save only those 'Still to be taught'-cells values and
assessor comment.
I haven't change nothing from 'basic forms' ... hope so.
'Click to go to Student List':
There is a picture 'ATTI and so on' ...
click that picture and ...
You'll see 'Student List'.
Gotta go now, coming back later ...
> Ideas ... questions?
ps. that Your 'D'-question ... this isn't ready!

 

[MonkeyNut]

Ok. I have highlighted the area that should remain the same in Orange. However what the macro is doing at the moment is picking up ALL the "Still to be taught" values. including the ones opposite the Yellow Unit Standard numbers. So when I click on a student name, it opens the student template and changes the Unit Standard Numbers to read, "still to be taught". The Yellow Unit Standard next to the Purple "still to be taught" should not be affected in any way. The Orange column at the top is the one which I put a mark into. The purple column next to the Yellow one merely changes to Competent or Not Yet Competent. Or in some cases, handed in, and so on. But until something occurs like a project handed in it has to show "still to be taught" and the Unit Standard numbers to the left have to be seen. Hope you can make sense of this.

 

[vletm]

Okay ... now I noticed ... ouch!
Now it saves/reloads those
"Still to be taught"- cells and 'blue'-"Assessor comment:"
Do it need to save/reload something else
like Weight, Level, Credits or are those 'fixed' values?
How about 'Issue Date:'? Is it always today?
Check, check ... and soon non editable cells should protect, how do You think?
Next ... maybe those printings, do You need PDFs? or only to printer?

There are possible to add more classes to same file ... need some settings.
> Ideas ... Answers ... Questions?

 

[MonkeyNut]

Weight, Level, Credits are 'fixed' values
Issue Date: Is always today
Yes Non editable cells need protection by Administrator
Need to Printer and to PDF

I am loving what you have done vletm.

Question? We offer many courses, and obviously Course content, and Unit Standards will change. As will Weight, Level, Credits in each Course case.
As admin would I be able to use the base of this VBA and just copy new content into it? Or how does this work?

 

[vletm]

1) Test to fill some students 'National'-sheet as normal and 'click' back to 'List'
Check that those filling will reload next time.
2) Protection: Good ... but only those cells which should! code use some!
3) Test to print some students 'National'-sheet ( read PDF-note! )
4) Many Courses: Are layouts same?
if same then somewhere could make list of fixed informations or
every course could have own sheet ... depends how many ... how different layouts ... and so on. If layout(s) change ... then it needs 'some' modifications.
5) Now You can add more 'class_codes' if those use same layout of 'National'-sheet ... just add more rows (have a backup)
6) I recommend some kind of 'automatic' backup for this =
'few rows code and ... there have to be folder for those'

> Many good ideas ... I just need details and I could try to help ... Okay?