Discussion in 'Business Applications' started by Nifty, May 8, 2006.

Not open for further replies.

Joined:
Jul 7, 2004
Messages:
10
I have a spreadsheet of students names, their subject results (F,P,C,D) for each stage for example STAGE 1 STAGE 2 STAGE 3
JOHN BLOGGS P D C C P C P P P P P P C P P C D
Each stage has a set number of subjects be it 5 or 6. What I would like to be able to do is
automate the spreadsheet so that as each stage is completed a master list is update with the stage the pupil is now enrolled and the pupil automatically transferred to the next stage.

Many thanks
NiftyK

2. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546

See the attached. Sheet1 is the results sheet, Sheet2 is the master.

Sheet2 has some 'step' formulas in B2:E2 (the 'notes' in B1:E1 try to explain what each formula does).

F2 contains a master formula, i.e. the step formulas bolted together, viz:

=INDEX(\$A\$11:\$A\$14,IF(COUNTA(INDIRECT("Sheet1!"&MATCH(A2,Sheet1!A:A,0)&":"&MATCH(A2,Sheet1!A:A,0)))-1<=4,1,MATCH(COUNTA(INDIRECT("Sheet1!"&MATCH(A2,Sheet1!A:A,0)&":"&MATCH(A2,Sheet1!A:A,0)))-1,\$C\$11:\$C\$14,1)+1),1)

If you move 'P' from F3 to F2 on Sheet1, E2 and F2 on Sheet2 will update to 'Stage_2'.

HTH,
bomb

#### Attached Files:

• ###### Stage_Tracker.xls
File size:
15.5 KB
Views:
93

Joined:
Jul 7, 2004
Messages:
10
Bomb #21

All going well I have sent a small sample of my spreadsheet. Perhaps it not all that well designed but it is simple to use. Simply we look up a Last Name and with Freeze Frames active move across to the stage where we need to enter marks.

NiftyK

File size:
20.5 KB
Views:
74
4. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
Sorry, I don't understand what it is about what you have currently that 'doesn't work'.

EDIT: (other than you don't actually need the RES columns)

Joined:
Jul 7, 2004
Messages:
10
Bomb #21

What I would like to do is from this Master have separate worksheets for each stage
so that I can quickly look at who is in what stage what subjects are outstanding etc.

Then ideally when I add any data in the master the worksheet is updated and if possible when a stage is completed by a pupil they are removed from that stage and their name inserted in the next stage.

I know its asking a lot but it would be great to automate the process.

Regards
NiftyK

6. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
With stage worksheets you may just be making extra work for yourself.

If you copy A2:E2 to A5:E5, you can then use AutoFilter on E5 to show students 'in' B, I, P, etc.

Take this a step further -- with autofilter of 'B', you can hide the INDUCTION/PROFICIENCY
/ADVANCED columns & define this as a custom view (named 'Basic', with hidden rows, columns and filter settings included in the view). Then you just switch between views as required.

7. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
BTW, Custom Views is on the View menu.

8. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
Actually it's pretty cool that you can add the Custom Views control to your toolbar. The attached screenshot shows the worksheet in 'Induction' view & about to switch to 'Proficiency'.

Rgds,
bomb

#### Attached Files:

• ###### stage views.JPG
File size:
97.2 KB
Views:
47

Joined:
Jul 7, 2004
Messages:
10
Bomb

Great that works a treat many thanks.

Sorry but how in the heck do add the Custom Views to my Toolbar?

Nifty

10. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
Rightclick a toolbar, select 'Customise'.

On the 'Commands' tab, select the 'View' category. In the 'Commands' listbox (on the right), you'll prolly be able to see the 'Custom Views' control without scrolling down. So just drag it from there to a toolbar -- or even the menu bar (File - Edit - View etc.) if you prefer. Actually, the menu bar's a good idea (?)

Joined:
Jul 7, 2004
Messages:
10
Bomb #21

Should have had more confidence in my ability - worked it out.
Sincere thanks for all the help it is truly appreciated as it will help me no end

Tks again
Nifty

As Seen On