+ Reply to Thread
Results 1 to 3 of 3

Link cells from table in list form

  1. #1
    Registered User
    Join Date
    08-11-2010
    Location
    Zurich, Switzerland
    MS-Off Ver
    Excel 2007
    Posts
    3

    Link cells from table in list form

    An Excel-illiterate teacher asks in desperation:
    I want to link cells from a weekly school timetable (including several separate classes) to create a list of each lesson per class.
    At the moment the weekly timetable runs left to right: the columns are headed by the day and date (Monday to Friday), while the times of each lesson form the first cell of each row. The timetable is repeated left to right to cover a whole semester, so that I can see when lessons are cancelled due to holidays or meetings or when a particular class has a test.
    I'd also like to create a list of all the lessons for each class (such an overview makes semester planning for each class much easier). If I enter a cancellation or other information into the timetable, the corresponding lesson in the list should be updated.
    The trouble is, that at the moment I only seem to be able to link each cell manually and individually, as the cells in the timetable are not contiguous.
    For example: Class 1d might have a lesson at 8 on Monday, 10 on Tuesday and 12 and 1 (i.e. a double lesson) on Friday. The corresponding cells in the timetable are, say, B1, C3, F5 and F6. For the second week of the semester: G1, H3, K5 and K6. And so on for each week of term. Is it possible to create a series of links (=B1, =C3, =F5, =F6, =G1, =H3, =K5, =K6 etc etc) in a single row without doing it manually for each of the cells involved (given that I teach several classes, there are hundreds of lessons per semester!)? I've tried using OFFSET and the fill handle but can't get anything to work...

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Link cells from table in list form

    Hi and welcome to the board

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    Doing this will ensure you get the result you need!

  3. #3
    Registered User
    Join Date
    08-11-2010
    Location
    Zurich, Switzerland
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Link cells from table in list form

    Thanks for your advice. I'm attaching a much simplified version ("Timetable model") of what I want to do. The overall timetable - where I would enter any changes such as cancellations, test dates etc - is on the first sheet ("Timetable") and the list of lessons for one of the classes (1d) is on the second sheet ("Class 1d" - there would be further sheets for the other classes). The second row of the second sheet is where I'd like to have the linked cells from the timetable - I've linked the first week manually, and am hoping somebody can offer me a way of easily linking the rest. In this example workbook I've only included the first four weeks of the semester.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1