Instead of Lablogger........requests spreadsheet

Discussion in 'Science Technicians - General Discussion' started by NTG, Jul 12, 2019.

  1. NTG


    I am posting this separately from the Lablogger Timetabling thread as it would keep things together as the plan is to show and guide folks how to use this for their own needs following feedback from the other thread.

    Let me stress at the outset that I am in process of migrating all out stuff to Lablogger as the time to do this is far less than writing the coding to have the spreadsheet do stock checks against requests !

    We originally has each teacher submitting separate and diverse spreadsheets for the poor Tech to transfer onto the master.....took at least a couple of hours and prone to error.

    So I developed my own spreadsheet (attached) where the Master (mine) gets populated from each teachers' sheet. The teachers do not need to type anything and use drop-down lists to select Room, Year, Lesson and the sheet automatically populates the equipment requirements for that lesson. This then populates the master so I get a complete view of the week, days and periods.

    I am happy to share this and guide people with how to use it for their own needs if not using Lablogger. It is straight-forward, but at times can be a bit 'Excel-ly" and some things have to be done in a certain way for it to work properly.

    My plan is to extend this thread with "How to...." guidance (probably separate Word documents) so that folks have a reference.

    Here's the file. You have to enable macros when asked. There is one in the workbook which clears all entries on the teacher sheets when they make a change to the Year group, etc.

    Please feel free yo have a play with it and let me know what of the "How to...." you'd like first. I have no plans to develop this any further so you'll have to take it "as is". Enjoy.

    And have a great weekend !!

    Attached Files:

  2. Thanks for posting this @NTG its really appreciated. I'll have a look at it when we're quieter on Monday and see what's what.
  3. I there maybe more steps you might want to explain before getting this one but...
    What is the procedure for inputting practicals and equipment lists onto the development sheet. We've tried simply rewriting them but the equipment list then fails to transfer into the teachers timetable when the practical is selected ... if that makes sense?
  4. NTG


    OK......makes sense for me to get started and answer questions as I go along. I appreciate you are anxious to get it up-and-running so will do what I can in this (quiet !!!) week.....

    Let's start with the Master Sheet. There is no need at this stage to understand that each 'cell' actually contains a formula which is used to fill each cell with data from the individual Teacher tabs (see the screenshot). ANY alteration to any of these cells means they do not work. This is why this sheet is 'protected' when teachers are filling it in !! I will return to this another time. In the diagram, you can see the formula that relates to cell for Natalie and Period 1.

  5. NTG


    Now, let's have a look at the individual teacher sheets, all of which are identical.

    The first three drop-down items are


    and the lists themselves are contained in columns A,B and C on the DATA VALIDATION SHEET


    You are free to change the contents to fit your needs, so can Change Room 31 to "Joule" for example. GO AHEAD AND TRY IT !! And check it works in the drop down lists on the teacher tabs.

    If you need more or fewer rooms, or a larger or smaller breakdown of sets or types of experiment, this is a techniques that will be covered when adding or removing lessons for the drop-down lists there to work properly.

    OK - to keep these sessions small.....I'll break off this one !

    Attached Files:

  6. NTG


    Now, let's cover the YEAR and LESSON drop down sections of the Teacher Tabs.

    You should see that selecting any of Years 7 to 11 changes the available options in the LESSON drop-down. Try it. Check that the Year 7 lesson options are different to the Year 10 choices. Choose Year 7 and then the lesson KS3 Y7 Being A Scientist L4 Measuring. You should then have the requirements populated and the link to the associated Risk Assessment (for this school) :


    Now suppose this should have been a Year 10 lesson. Now change this in the drop down and you should find that the Lesson, Equipment Required and Risk Assessment boxes are automatically cleared. It is the same if you select the cell and press "DEL" - the selections are cleared. You should find that the lesson options for the LESSON drop down are completely different. Try it.

    The next session will cover where these options come from.
  7. NTG


    We now need to understand where the options for the YEAR and LESSON drop-down lists come from. These are similar but not identical to how they work for Room, Practical / Demo, Sets Required which are covered in an earlier session.

    NOTE : I am not covering how to change these just yet as there is an easy (well, less laborious !!) way to do this after you have your lesson equipment requirements sorted out !!

    OK - the list for the YEAR is taken from the DATA VALIDATION SHEET - Columns H to N. Note that this is a HORIZONTAL list (of the column header / name) with the lessons for each year listed VERTICALLY under each.


    Note also that the horizontal list names have no spaces. Some versions of Excel for some wierd reason doesn't like spaces in horizontal lists !!

    OK - so that's covered where these come FROM......but not how to alter them, just yet ! We now need to see where the equipment lists come from....
  8. NTG


    Right, turning now to the basics of getting your own lessons into the sheet and start getting it to work for you.

    The first thing to note is that, in the previous 'lesson' , the vertical lists under each of the headers "Year7", "Year8", etc.are 'split out' from the combined list of all Lesson Names which are contained in a large table in the "Equipment Lookup DEVELOPMENT" sheet.


    For the time-being (and unless I get a request at a later date, I will not go through the process of inserting hyperlinks to the Risk Assessments as you may have alternative systems for RAs. My focus will be on Column A and Column B.

    In column A you can see the name of the lesson. and in Column B all the equipment associated with the practical in that lesson (on a per experimental Group basis - so 5 sets will need five time this). Scroll down to the bottom of column A. In this case, our lessons total 534, ending in row 535.....many are duplicated.


    The structure I am using here is a LOOKUP table, specifically a vertical lookup , or VLOOKUP. Here there is a formula which takes the name of the lesson from the teachers' drop down list and compares it with this Table, stepping downward until it matches an entry in the table. When it does it then automatically populates the Equipment Requirements section with the contents of the cell in Column B, i.e. the equipment required for that lesson.

    Try it. One one of the Teacher tabs, select Year7 and the lesson KS3 Y7 Being A Scientist L10 Solubility and temperature, as you can see in the table above to get (in the teacher table).


    In my case it also displays the links to the RAs that the teacher is deemed to have read.

    Have a look at the Master Sheet to check the details are now listed there......

    More to follow ....!
  9. NTG


    OK, so now you want to change these for your own lessons.

    There are three stages to getting this done
    1. Preparing the lookup table and validation lists to accept (I am assuming) wholly new names and lists
    2. Getting your lesson names and associated equipment requirements into the Table in the Equipment Lookup DEVELOPMENT sheet and;
    3. Updating the lesson names that are displayed in the drop down lists (which MUST be identical)
    So I will split these into three separate lessons which I think makes it easier.

    And yes, all of the previous ones are needed to have a grasp of what's going on to help troubleshoot in the future......sorry !!
  10. NTG


    Preparing the worksheet for a new set of Lessons (PART 1)

    We are now going to delete all existing data in the worksheet that you have, rather than type over what is already there. I think this would be confusing.

    • Select the DATA VALIDATION SHEET tab
    • Select all the cells indicated and then press "DEL" to delete the contents
    • Dont forget to save your work (perhaps as a separate file !!) as you progress through this...../

    • Now select the single cell H2
    • Press and hold down these keys in order CTRL + SHIFT + Down Arrow which should rapidly select all the cells with data in the H column underneath Year7 (this fast select is a nice trick in Excel useful for long lists rather than scrolling)
    • To clear all the contents (but not the formatting - do not worry about that) now press "DEL"
    • Use this fast select and delete technique to clear columns (but not the headers) for Years 8 to 11 inclusive
    • Now clear the items under SCIENCECLUB and SCIENCEWEEK
    • If necessary, change the names for SCIENCECLU and SCIENCEWEEK or even delete them entirely
    • You should now have something like this
  11. NTG


    Preparing the worksheet for a new set of Lessons (PART 2)

    Now switch to the Equipment Lookup DEVELOPMENT sheet. We now need to delete all these contents as well.
    • Select Cell A2
    • We are now going to Fast Select the whole table, except the headers.
    • Press and hold, in turn, CTRL + SHIFT + Right Arrow + Down Arrow and you should fine you have selected all the wat down to row 535 and Column D
    • Delete all contents by pressing "DEL" and the contents of the cells will be gone.
    Please note that the table still has 534 rows (indicated by the blue/white alternate shading). We can adjust the table size later as I am for now assuming you have a lot of lessons to enter !!

    Save your working file and progress so far.

    Now go back to any teacher sheet and you will find that only the Year drop down list actually works. As you would expect as we have cleared data for all other elements.

    Your sheet is now ready to enter your own lessons.
  12. NTG


    Preparing the worksheet for a new set of Lessons (PART 3)

    Getting the Rooms Drop-Down working again !

    You will undoubtedly have different room designations. Select the DATA VALIDATION tab and then Cell A2 underneath Room.

    Type in all your rooms that you normally use, plus an OTHER room to get something like :


    OK , so here's another EXCEL ALERT !!!

    Have a look at any of the teacher sheets and check to see that the Room drop-down now reflects what you have entered. There will be three cases. Where you have entered eight rooms all will be well and you will see all of them.

    In my case I entered seven and the drop down has a spare 'space' shown by a blue bar underneath OTHER (sorry the snipping tool I have here is poor at grabbing images needing to select a drop down)

    If you have entered more than eight, then anything after the eighth will be missing.

    This is because the region originally chosen for the "Data Validation" List only covered 8 cells. We need to change that. But first you need to find the Data Validation Tool in your version of Excel. Mine is on the DATA ribbon


    Before selecting the tool, you need to check the range of the data in your list of rooms. In my case above you see the room names are in Cells A2 to A8 so my range is A2:A8. Make a note of yours.

    Now go back and select the Data Validation option/tool. Here you should see :


    And in the Source window you will see a formula. Don't worry - you can see that it is referrencing the DATA VALIDATION sheet and then the range is $A$2:$A$9. To make this correctly point to my data range I simply need to change the "9" to "8". Yours might be 10, 12 , 15 or whatever your Range is. Click insiode the window after the 9 and make the appropriate change. Now press OK to save it.

    Now check the dropdown list is correct and shows all the options with no extra items (blue bars).

    Phew........that's about enough for today.....

    And don't worry, that's about as complicated as it gets from an Excel point of view for you !!!

    have a great evening.......
    1 <3 Daphnia likes this.
  13. NTG


    Morning all !! Today, I'm going to try and get through the majority of the stuff needed for you to get this up-and-running with your own lessons, etc. So far, and I will continue, I've adopted an approach that should enable you to fix most of the common issues I have come across - though I will cover some others arising out of "biological interface errors" (i.e. the component of the system between the keyboard and the chair !!!)

    In the last session we covered how to adapt the drop down list for the Room and making sure the system allowed the correct number of choices to be made.

    What I did not mention is that you have to work through that process for each of the individual teachers' sheets. Apologies ! I could have coded that as a macro, but its quicker to do it manually.

    You also need to follow the same process to alter your drop-down lists for Practical/Demo/etc. and Sets Required - here's my example


    Don't forget to check and adjust the Range for each of these and do it in every teachers' worksheet.

    That should keep you busy for a while. Don't forget to check that each drop-down list on each sheet works correctly !
  14. Thank you so much for your help so far. I *think* we're getting to grips with it!
  15. NTG


    The next step is to get the drop down list for the Year working correctly. You may recall I mentioned earlier that this is a horizontal list and is on the DATA VALIDATION SHEET and is in cells H1 through to N1 (Year7, Year8, Year9........SCIENCEWEEK).

    The first step is to decide what you need here, though I would recommend leaving Year7-11 in place and perhaps simply change the SCIENCECLUB and SCIENCEWEEK ones to meet your needs. Do that now.

    Make a note of the Range (horizontal) of your list (in my case H1:N1) - just as we did for the vertical lists

    Now switch to one of the teacher sheets and select the first cell G2 under the Year header. Then click on the Data Validation tool and adjust the Range in Source if necessary :


    You may have noticed the "Apply all changes....." check box just above Clear All ? You can of course select this and Excel should apply the same changes across all the teacher sheets, but I have found this occasionally unreliable (though have not discovered the reason, yet!). Because of this I tend to do the changes to each individual sheet.

    Make sure you have done this and checked the Year drop-down works for all the teacher sheets.

    We are about ready to get your own lessons on !!
  16. NTG


    I suspect your sheet, as you hinted before, is filling up with the incorrect equipment ? I will resolve this in the next few sessions !!
  17. NTG



    You may by now be thinking that the next step will be to sort out the drop down lists for each of the Year7, etc. which is a reasonable thought. However, due to a "feature" of Excel, it is easier to do the lessons and equipment requirements first.

    Also, there is some coding behind the scenes that adjusts what is visible in the Lesson drop down so that you only see the lessons for the particular year you have chosen in Year drop-down. Don't worry, you don't have to ever change this !!! This is just to let you know there is other stuff going on. The coding also clears the Lesson contents if you change the Year which prevents the wrong equipment list being filled.

    You now need to select the Equipment Lookup DEVELOPMENT sheet. My focus will be on the first two columns only for the rest of these sessions. I can return to the others if necessary in the future.

    IMPORTANT NOTE : This is the main table used for the LOOKUP that fills the teachers sheets and thus the master requests sheet. This will only work if the following apply :
    1. Each lesson must be a unique name; AND
    2. There must be no blank cells in the list of lessons top-to-bottom of the list; AND
    3. The list must be sorted alphabetically otherwise results are erratic.
    I am adopting the approach that you will need to type each lesson name individually (as I did !!), but have some form of list for the equipment needed (or you are going to type them in.....). However, if you have perhaps a spreadsheet with a list of the lesson names, or can extract them from a list in a Word document, then you should be able to copy-and-paste into the Lesson name column.

    There is no need to worry about the alphabetical sorting at this time......I do this at the last step as it is generally done after new lessons are added (or removed)

    I have also found it good practise to indicate the Year for the lesson name within the name itself, so I would use Y7 as the initial prefix for all Year7 lessons. Copy the following lesson names into your spreadsheet


    Notice they are not alphabetically sorted at this stage. This is normal when adding lessons and is resolved at one of the last steps (BUT DO NOT FORGET TO DO THIS STEP !!!)

    If you are typing the equipment lists into the cells B2,3,and 4 - then go ahead and do so.That will be fine.

    The problem comes where you have a bulleted list of equipment from either a Powerpoint of the lesson or a Word document, for example
    • Balloons
    • Bike pump
    • Inflatable atlas
    Initially you would think that you could copy-and-paste directly into the cell. However, Excel interprets that each bullet point will go in a separate cell and will therefore populate the lower cells with the wrong equipment ! You can work around this by copy-and-pasting into that is called the Formula Box :


    That works OK - but you will need to remove any special characters that are also pasted into the box and the cell.

    I've typed the requirements as follows - please copy them to your sheet


    Next we'll go through how to get these and the drop-down working properly
  18. NTG



    So, to get these new lessons into the drop down lists and then, when selected, have the equipment requirements populate correctly, we have to do a little bit of tidy-up. These steps are important and, if forgotten, will stop the sheet working properly !!

    The very first thing is to recall when we deleted all the lessons from the original spreadsheet, I said that the Table is still 534 rows long (indicated by the alternate shaded rows). We deleted the contents but not the rows of cells. The table is far larger than the current data set ! First step is to remove the additional rows
    • Select cell A5 immediately below Y7 Alkali metals demo
    • We need to Fast-Select all the empty rows. Do this by pressing and holding CTRL + SHIFT keys and then pressing in turn Right Arrow then Down Arrow. You should then see the following

    Which shows all the rows in the table have been selected.

    To remove these you now need to Right-click to display a new menu and then click on Delete and then click on Table Rows. (apologies, no screen shot for this as my system doesn't do this type)

    NOTE : the Del does not work in this case of a Table

    You should end up with a Table containing only the three lessons we entered. Note the alternate shading below Row 4 is no longer present.


    Save your work so far.

    Attached Files:

  19. NTG



    So we now have a Table of lessons with equipment requirements. This next bit may be a little confusing, but will become clearer once I have gone through adding for Year8 and updating the whole spreadsheet. Nearly there - honest !!

    We need to do two things here - make the drop down list show the Year7 Lessons and ensure the correct equipment is populated in the Teachers' sheet and also the Master Sheet.

    Remember the rules about LOOKUP TABLES ?
    1. Each lesson must be a unique name; AND
    2. There must be no blank cells in the list of lessons top-to-bottom of the list; AND
    3. The list must be sorted alphabetically otherwise results are erratic.
    The first thing to do is to alphabetically sort the Table - which is easily done in a Table by clicking on the indicated button below and then sort A-Z which is the first option :


    And the Table is sorted.

    Now to populate the drop down list, as the lesson names must be identical, then we need to select all the Y7 lesson names and copy them using the standard windows copy command CTRL + C. Make sure you have selected only the lesson names and not the equipment as well !!

    Next step is to switch to the DATA VALIDATION SHEET and then select the cell immediately underneath Year7


    Now paste the lesson names using CTRL + V


    Make a note of the Range of the lesson names - here it is H2:H4. We again need to tell Excel where to look for the drop-down list.... and that's the next session
  20. NTG



    In general there will be perhaps dozens of lessons for each pf the Year Groups, so a validation list (like we used earlier) is a bit cumbersome and is better is it is altered just the once rather than across each of the teachers' sheets. Here I use a function of Excel called a Named Range. Don't worry as it is all set up and just needs adjusting in a similar manner to the other drop down lists.

    It varied by version of Excel exactly where this is but you are looking for the Name Manager tool - mine is here - clicking it opens up a new window where I have selected Year7 :


    Note that the other items are also already set up. All we need to change is to tell excel what the correct range is.

    This is kept in the box at the bottom Refers to....where you can see that the range at the moment is H2:H56 (ignoring the $ symbols). From above I note the actual range is H2:H4 so I need to change "56" to just "4" - so make that change, press ENTER and the click on the Close button.

    NOW TO TEST IT !!!!

    Choose any of the Teachers' sheets. Choose whatever takes your fancy from the Room/Demo/Sets drop-downs and then Select Year7 from the Year drop-down.

    Now use the Lesson drop-down and you should see the three lessons we entered.

    Choose the Alkali metals demo.

    The equipment requirement will automatically populate !!


    Change Year7 in the drop-down to Year8 and the lesson and equipment requirements will clear - which is exactly what we want !

    Re-select Year7 and the Alkali metals demo.

    Verify that this whole lesson, sets, etc. has populated the Master sheet


    Works for me ! Hope yours does too........