+ Reply to Thread
Results 1 to 5 of 5

Creating an updated schedule/table based on lines of data.

  1. #1
    Registered User
    Join Date
    01-06-2012
    Location
    Lubbock, TX
    MS-Off Ver
    Excel 2007
    Posts
    13

    Creating an updated schedule/table based on lines of data.

    I'm trying to figure out a way to take a line of data consisting of:

    Room number / Name / Time of visit

    I want to be able to enter the data (and some additional data) about the people being visited in a line, and have it automatically generate a room schedule with the time on the left and the room number across the top, but have it be complete with all possible times (in 15 minute increments) and all the room numbers across the top, even if there are no scheduled visits.

    Anybody know what kind of system would allow me to do this? So far I havn't had much luck with pivot charts, but it could be my general inexperience with them.

    These will need to be updated daily, and usually with very short notice, so moving things around on a big schedule would be somewhat inconvenient, and we need to record more data than we could reasonably put on a single schedule anyway. I'm trying to make this as little investment of time as possible to keep the books updated.

    Ex. Data Line:
    3 / Jon Johnson / 0815 / Visitor Name



    Ex Table (Ignore the underscores, I couldn't set the spacing properly without them):
    _______1 ________2___________ 3 ____________ 4(etc.)
    0800
    0815 _____________________ Jon Johnson
    0830
    (etc.)

    Any help would be greatly appreciated, even if it's just pointing me in the direction of the tools that could do this.
    Last edited by Lord451; 02-22-2012 at 05:56 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    12-05-2011
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010 & 2013
    Posts
    308

    Re: Creating an updated schedule/table based on lines of data.

    Hi Lord451

    I think I've done what you need.

    Check the attached.

    Cheers, Rob.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    01-06-2012
    Location
    Lubbock, TX
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Creating an updated schedule/table based on lines of data.

    Thank you, it seems to work great. I'm still looking through to figure out exactly how you set it up so I can learn some new tricks, but I really appreciate it.

  4. #4
    Registered User
    Join Date
    01-06-2012
    Location
    Lubbock, TX
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Creating an updated schedule/table based on lines of data.

    Ok, after playing with it a little more, I realized that this is EXTREMELY CLOSE, but not quite what I need.

    Whenever I try to move the data around (the lines), something gets borqued with the formulas. I'm still not exactly sure what's going on with the sum product, but when lines are moved everything goes from a blank space to a 0.

    It works if I move it to another sheet, but not if I move it lower in the sheet. What I need is the big table up top, and space for the lines below on one page.

    Also, a brief explenation of what exactly you did with the sumproduct formula would be greatly appreciated. So far I can't pick apart exactly why I can't move the data set; I always wind up with a 0.

    Anyway, thanks.

    Edit: Did some tweaking. Apparently when the lines move over, it changes what the formula is looking at; doing it one line at a time the names get rearranged where they should be and when they fall off they become 0s, but they also move out of place before they're dropped.
    Last edited by Lord451; 02-22-2012 at 06:22 PM.

  5. #5
    Valued Forum Contributor
    Join Date
    12-05-2011
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010 & 2013
    Posts
    308

    Re: Creating an updated schedule/table based on lines of data.

    Hi Lord

    There is a slight modification required to the formula if the data doesn't start in row 1. Drop the row numbers out of the first INDEX command, so cell Sheet1!B2 becomes:
    Please Login or Register  to view this content.
    Hopefully this should solve your problem.

    I have also incorporated a check on Double Booking in to the sheet. This makes the formula look pretty horrendous. If you don't want it, just revert to the formula above.

    I have moved the lines on to the bottom of Sheet 1.

    I have also done a detailed explanation of the SUMPRODUCT function and how I have used it. Refer to the file Timetable Explained attached. Also attached is a revised original without explanation.

    Hope this helps.

    Cheers, Rob.
    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