How can I check spreadsheets in multiple subfolders then filter on user criteria

[Dischordant]

Hi guys - I work as a Secondary school teacher and we have a spreadsheet for each teacher with all our classes on it and the students names for each class.

In this spreadsheet we can mark on whether a student is struggling with progress or behaviour and what we are doing to address it. Each department then has a Summary spreadsheet that pulls out any students who are having trouble in their department so the Head of department can keep an overview.

What I'd like to now create is a spreadsheet the head of year/key stage can use that will ask them the year group of the student they are interested in and how many amber or red flags they want to look for. (E.g. students in Year 7 who have over 4 subjects listed with an R).

The spreadsheet it would need to check are each in a departmental folder.

E.g.

\\Student Monitoring\Head of Year-KeyStage Spreadsheet.xlsx

\\Student Monitoring\ICT\IT Summary Spreadsheet.xlsx

\\Student Monitoring\PE\PE Summary Spreadsheet.xlsx

\\Student Monitoring\English\English Summary Spreadsheet.xlsx

...etc.

There are about 14 different subject areas it would be pulling from.

What is the simplest way for me to achieve this please?

Many thanks for any and all help!

Darren

[xladept]

If the Spreadsheets are in the same folder, then your best bet is to "run the directory" - but, for us to write a routine for you, we'd need an Example Sheet - hopefully they're all formatted similarly!!

[Dischordant]

Thank you Xladept - Can I post an example of 2 of the spreadsheets here or do I need to host them?

[xladept]

You can post them here - go to advanced and click on the paper-clip icon.

[Dischordant]

Music Department Summary.xlsmScience Department Summary.xlsmPS.xlsx

Please allow me to apologise for the inelegant way this system is created - if it were up to me it would all be in a database but this was how it was requested and my excel knowledge is so lacking I did what I could =p

The summary sheets already accumulate all of the teachers students who are of concern successfully so I then just need to be able to check for a name appearing in more than one (dependent on criteria chosen) of the department checklists.

E.g. SMITH John has been misbehaving in two of his subjects - therefore on the proposed spreadsheet when a drop down is set to "2" it would return the comments from both those subjects for SMITH John (and any other students with 2 or more Red).

Thank you!!

Darren

[xladept]

Hi DisChordant,

'What I'd like to now create is a spreadsheet the head of year/key stage can use
'that will ask them the year group of the student they are interested in
'and how many amber or red flags they want to look for.
'(E.g. students in Year 7 who have over 4 subjects listed with an R).

Please Login or Register  to view this content.

Please Login or Register  to view this content.

'I was unable to test the whole routine since I couldn't update the source sheets
'But if you want to send me some Departrment Summary test data, I'll follow through


P.S. The Summary book has 4 extra sheets: Compilation, Red, Amber, Outstanding each with Headers - I'll Attach an example

DisChordant.xlsm

[xladept]

Hi again,

I made up some test data and now it seems to be working - try this:

Please Login or Register  to view this content.

I don't unhide the rows in the summaries, it's unnecessary - here's discord Discord.xlsm



Since the posting above - I've fine tuned the program - this is the latest version:Discord.xlsm