+ Reply to Thread
Results 1 to 11 of 11

Extracting Data from table to calendar view

  1. #1
    Registered User
    Join Date
    09-05-2019
    Location
    Batesville, Indiana
    MS-Off Ver
    365 ProPlus
    Posts
    8

    Extracting Data from table to calendar view

    I need help extracting data from my table in excel into a calendar view similar to my attachments. I am attaching my spreadsheet that has all of my data in it as well as a PDF showing how I would like to have the data extracted. I would like to continue adding data to my spreadsheet and every 2 weeks where I can choose my dates in the calendar view.
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Extracting Data from table to calendar view

    Presumably you don't want to show any data if you have OFF in the Route column, but are there any other codes in there that you don't want to show? Like PTO, or OPEN, or even blanks? Or, do you want to show everything?

    Pete

  3. #3
    Registered User
    Join Date
    09-05-2019
    Location
    Batesville, Indiana
    MS-Off Ver
    365 ProPlus
    Posts
    8

    Re: Extracting Data from table to calendar view

    I think I want to show everything. This helps other team members know if someone if OFF, they might be able to call them if they need someone to work for them versus if they are on PTO they might be unavailable. The OPEN is an open position that needs to be filled.
    Thanks So much!!!

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Extracting Data from table to calendar view

    I've set this up for you in the attached file, where I have added a sheet called Calendar which presents the data in the format you outlined in the PDF file. I've also tidied up your Lists sheet a bit to make it a bit clearer.

    I've added two extra columns to your main data table, which help to identify particular records. Various other formulae in the Calendar sheet make use of these in order to form the layout. In the Calendar sheet you can specify the start date in cell F2, which should be a Monday - you will be warned if it is not in the cell below, which will turn red.

    I've allowed 3 extra rows below each Shift block, to accommodate having more data in other weeks.

    Hope this helps.

    Pete
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-05-2019
    Location
    Batesville, Indiana
    MS-Off Ver
    365 ProPlus
    Posts
    8

    Re: Extracting Data from table to calendar view

    I believe this is exactly what I am looking for. Thanks so much!!! I will review more tomorrow. Thanks again!!

  6. #6
    Registered User
    Join Date
    09-05-2019
    Location
    Batesville, Indiana
    MS-Off Ver
    365 ProPlus
    Posts
    8

    Re: Extracting Data from table to calendar view

    I am having another issue. When i was entering this this two week schedule, late, I had an employee leave. When i replace her name in the data tab with "OPEN", it shows her name still under third shift as well as the open position. It is like the calendar view is seeing her name from the previous two weeks schedule.

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Extracting Data from table to calendar view

    I've been away on holiday, so to refresh my memory about this please attach a workbook which shows the problem, and I'll take a look at it tomorrow.

    Pete

  8. #8
    Registered User
    Join Date
    09-05-2019
    Location
    Batesville, Indiana
    MS-Off Ver
    365 ProPlus
    Posts
    8

    Re: Extracting Data from table to calendar view

    If you look on the calendar tab, you will see Bohlke, C, Perkins, M, and OPEN under 3rd shift. The date I chose for the calendar to start is 8/19/2019. If you go to the DATA tab, you will notice that I started my next series of the schedule at line 455. When you get to line 469, Bohlke C was replaced with and OPEN position. The calendar tab is pulling staff names from before the start date on my calendar.
    Attached Files Attached Files

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Extracting Data from table to calendar view

    I'm a bit confused. The row you refer to (469) relates to 2nd September (Monday), and the calendar (row 51) only goes up to 1st September (Sunday).

    Row 94 of the calendar sheet is actually picking up the data from row 25 (et seq) of the Data sheet.

    Hope this helps.

    Pete

  10. #10
    Registered User
    Join Date
    09-05-2019
    Location
    Batesville, Indiana
    MS-Off Ver
    365 ProPlus
    Posts
    8

    Re: Extracting Data from table to calendar view

    So i did have the date incorrectly set on the calendar,G2. It should have been set to 9/2/2019. Once I do this, i would like to not see Bolke, C. This person is not scheduled during the calendar time from 9/2/2019 through 9/15/2019 so they should not show up on the calendar.

    I guess regardless of the setting of the date in the calendar view, I would like to only see the staff listed that are in that date range on the data tab. Hope this makes since.

  11. #11
    Registered User
    Join Date
    09-21-2019
    Location
    UNITED STATES
    MS-Off Ver
    2013
    Posts
    4

    Re: Extracting Data from table to calendar view

    Your post was so useful.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 22
    Last Post: 09-08-2019, 04:59 PM
  2. Populate Calendar View from Data Table
    By Alliooop52 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-10-2017, 01:48 PM
  3. Map Schedule Data onto Calendar View Generated by Macro
    By howaboutno in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-20-2016, 03:45 PM
  4. Replies: 9
    Last Post: 08-09-2016, 01:19 PM
  5. calendar view,, and table??
    By mark692 in forum Excel General
    Replies: 1
    Last Post: 04-17-2012, 04:26 AM
  6. Replies: 2
    Last Post: 04-13-2012, 01:16 PM
  7. [SOLVED] How to create a calendar view from data in Excel?
    By MLM in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-07-2006, 10:10 AM

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