Want to add columns to data list which auto populates to calendar

[avib]

Hi,

I have a calendar which is auto-populated from a data list on a different sheet in the same workbook. I actually downloaded this file from another thread on this site, but I can't find that thread again. This calendar is almost exactly what I need, but I'd like to add columns to the data list, such as "client." I'll attach the file with a couple of rows of sample data in the list.

I am fairly novice at excel, I know basic formulas but the ones in this calendar are more advanced than I can work with alone.

I wish I could give credit to the person who created this file, but unfortunately I x'd out of the page after downloading the file. Thank you in advance for the help, I'll check back tomorrow morning!

-avib

[Pete_UK]

That's one of my calendar files. Take a look at this link:

http://www.excelforum.com/excel-new-...ml#post3662844

which lists a few other links - this first of these will probably give you the kind of thing you are after.

Hope this helps.

Pete

[avib]

Thanks Pete_UK! Both for your timely reply to this post and for creating and posting the calendar I first downloaded as well. I've found a couple of different calendars in those posts which are helpful. I've taken one of them and changed it slightly (basically just the fields next to the calendar itself) and it is nearly perfect, but I have one last question - in the future I may want to add more columns, such as "location" or "assigned to", is there a simple way to do that? I looked through the links you provided, and although I found a very suitable calendar, I didn't see anything that would explain how to make this addition. Also, if this is possible, and I can add this column, would I be required to
have content in those cells for the calendar to work, or could I leave some blank and
fill some in?

Thanks again for all your help!

[avib]

Also, one more question - in the first calendar, I can enter any text into the data list to be populated into the calendar. In the second, there are drop down menus that require you to choose from a preset list - is there a way to set it up like the first calendar rather than having restrictions on what can be input?

Thanks again

[Pete_UK]

Each of the calendars I listed were produced for the particular poster's requirements - I think I also posted one in the past which had up to 4 items of data that needed to be displayed, and this was done in separate columns on the calendar sheet. In the particular one you have chosen there was a restriction on what could be selected in the columns on the Activities sheet so that those items could then be filtered out in the Calendar. You could get rid of the restrictions (i.e. the data validation), and then be able to put whatever description you liked in the activities sheet, but then you would lose the ability to apply filters. In the particular calendar you posted, the two items of data were shown together in one cell in the calendar, but you could have more than one column per day and display multiple columns of data from the activities sheet.

If you ask me nicely (and give me all the details of what you want to achieve) then I'll be able to put a bespoke calendar generator together for you, but I'm off for a long weekend tomorrow so it will have to be next week.

Hope this helps.

Pete

[avib]

Oh, ok I didn't realize the restrictions were what allowed for the filtering, which is definitely a feature I would like to keep. If you'd be willing, I'd definitely appreciate your efforts in creating this calendar for me.
The calendar I'm looking for would be very similar to what I have here already, except with a few more columns included.
The columns I would like to include in the Activities Sheet are: Client, Event/Service Item, Date, Time, Travel?, Assigned To, Location, and Notes.

My plan is to use each field name to filter the calendar for what I need, ie filtering the Travel column to "Yes" to view a Travel Calendar, Assigned To column filtered to a particular employee in order to view their individual calendar, etc.

The last thing that I would like to see from this Activities Sheet would be the ability to also populate a table/timeline type chart, as well as the calendars. The chart I have in mind would have the each Client from the client column populate the top row as Field Names, the Event/Service Items as the first row as row titles, and the rest of the info (date, assigned to, time, etc) as the info in the cells that correspond in the spreadsheet. This is a bit different of a task than what I've been asking for previously, so I understand if the Activities Sheet data list wouldn't function quite like this, but if it is possible, I'd really appreciate this feature as well. I've included a basic template of what I think that destination sheet would look like, let me know if that's a possibility.

Thank you very much for your help on these issues, already I've had a lot of progress through this thread and you've been extremely helpful.

-avib

[Pete_UK]

I'm back, but I haven't a clue from what you posted what your data is like or how you intend it to be displayed in a calendar format.

Please put another mock-up together which includes some typical data and show how you would like this data to be displayed (i.e. a before and after sheet)

Pete

[avib]

Sorry about my less than clear post, I've created a very basic version to try to show what I am looking for. Although they have no real function yet, I've entered in the titles and some data for the extra columns which I'd like to add. I've also added a sheet with an Event Tracker spreadsheet, which is essentially the same data from another viewpoint. It would group the data with each column representing a different client's events.
On the calendar sheet, I'd like to be able to filter the calendar by the different columns to create more specific calendars, like a calendar just for Client 1, or Tim's Calendar. One important calendar I'd like to be able to view is a "Travel Calendar." I was thinking that any event involving travel would be marked "Yes" in the Travel column, and then I could use that to filter the calendar to show just events that require travel.
Thanks again for your help, please let me know if this was a clear enough picture of what I'm looking for.

- avib

[Pete_UK]

I have studied your requirements and I think I know what you are hoping to achieve. First of all, the event-tracker sheet - I've added a helper column (M) in the Activities sheet with this formula in M2:

=E2&"_"&D2

which is then copied down. Then in B5 of the Event_tracker sheet I have this formula:

=IF(ISNA(MATCH($A5&"_"&B$2,Activities!$M:$M,0)),"",TEXT(INDEX(Activities!$F:$F,MATCH($A5&"_"&B$2,Activities!$M:$M,0)),"m/d/yyyy")&" - "&TEXT(INDEX(Activities!$G:$G,MATCH($A5&"_"&B$2,Activities!$M:$M,0)),"hh:mm")&CHAR(10)&INDEX(Activities!$I:$I,MATCH($A5&"_"&B$2,Activities!$M:$M,0))&IF(INDEX(Activities!$H:$H,MATCH($A5&"_"&B$2,Activities!$M:$M,0))="",""," (Travel)"))

which can then be copied across and down to give you what you want.

As for the calendar sheet, if you study the relationship between this and the Activities sheet you will see that there are 3 helper columns (A B and C) which give the ability to filter by Client and by date (month/year). If you want to be able to filter 4 fields then you will need many more helpers and the formulae in the Calendar sheet will become quite horrendous. Although this could be done, I think it is beyond the scope of what you might reasonably expect in a help forum, and I don't have the time at the moment to devote to this task, so I will have to decline from developing this any further.

Hope this helps.

Pete

[avib]

For sure, I appreciate the help you were able to provide me with Pete - thanks again, I'm very glad I came to this forum.