1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Spreadsheet

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

Thread Status:
Not open for further replies.
Advertisement
  1. Nifty

    Nifty Thread Starter

    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.

    Can anyone help me please.

    Many thanks
    NiftyK
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Lack of clear info (i.e. data layout) makes it difficult to say very much about this.

    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:

  3. Nifty

    Nifty Thread Starter

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

    Many thanks for your assistance.

    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
     

    Attached Files:

  4. bomb #21

    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'. :confused:

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

    Nifty Thread Starter

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

    Thanks again for your assistance.

    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

    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

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    BTW, Custom Views is on the View menu. ;)
     
  8. bomb #21

    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'. :cool:

    Rgds,
    bomb
     

    Attached Files:

  9. Nifty

    Nifty Thread Starter

    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

    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 (?)
     
  11. Nifty

    Nifty Thread Starter

    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
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/465844

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice