# Office 365 >  >  Extracting Data from table to calendar view

## neffd

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.

----------


## Pete_UK

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

----------


## neffd

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!!!

----------


## Pete_UK

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

----------


## neffd

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

----------


## neffd

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.

----------


## Pete_UK

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

----------


## neffd

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.

----------


## Pete_UK

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

----------


## neffd

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.

----------


## enerkeylog

Your post was so useful.

----------

