Auto-populating calendars using excel - marketing purposes

Hello,

I am looking to use excel to auto-populate a few calendars. They are marketing based. I'd like to be able to input all information on one sheet (i.e. run date, vendor, ad title, cost, and a few other items) and then have a calendar pre-fill that shows which ads are running on given dates. I tried using a vlookup and got it to work except for the problem of multiple ads running on the same day.

once the calendar would populate id like to get a weekly, monthly and YTS cost totals.

On other pages i would like to just filter things by say ad or sponsorship and show a running list of the items, but the calendar is where i am really hung up.


I've attached a copy some data and potential layout.

Thanks for any help you may be able to provide.

Copy of 2012 Budget for Marketing.xls

This may not entirely suit your needs, but here is a calendar I have previously made. It allows for multiple items per day (up to 5).

This calendar is a really great start.

First in the month drop down...at least when i open the file it seems to be missing some months.

Second is there a way i could get it to pull over the description column and another column as well? (colum C?) I'd like to be able to put the cost in there is that would work out.

Thanks.

Here is a modified file.

The drop-down was due to the fact I had inserted a bunch of rows. This calendar originally was meant for only one item per day. I had added the rows to suit your request of multiple items, and I forgot that the drop-down's source data was on the same sheet.

I added in the ability to concatenate more data columns into the cell. To do this, I inserted some helper columns for each day of the week. This helper column is used to calculate the row number. Then INDEX uses that row number to grab the data. I did it this way so that the workbook wouldn't need to calculate the row number multiple times (once per data column you wanted displayed)

This is huge help.

Can you just tell how to change the formating from time as you have in the example to currency?

Here is an updated version. I cleaned it up some. I moved the helper columns all to the right of the calendar. This allows for easier copy and paste of formulas. I also added a row. Some months require six weeks to display all days (see Sept 2012 and Dec 2012)..

This is pretty much exactly what i was looking for. Just some minor tweaking needed and I'll be set.

Thanks for all the help.

In the processing of tweaking it I realized I'd like to add another column of data that the calendar pulls. I tried adding tot he formula but without success.

Current: IF(ISERROR(A6),"",INDEX(CalendarItemDescriptions,A6) & " - " & TEXT(INDEX(CalendarItemTimes,A6),"$#,##0.00"))

Attempted: IF(ISERROR(A6),"",INDEX(CalendarItemDescriptions,A6) & " - " & INDEX(CalendarItemVendor,A6) & TEXT(INDEX(CalendarItemTimes,A6),"$#,##0.00"))

I ended up with a blank cell.

Any suggestions?

Thanks

If you are using the latest version of the file, then referencing A6 is no longer valid. Because I moved the helper columns off to the right, A6 is now J6. I am guessing you got a circular reference error.

Try:
Attempted: IF(ISERROR(J6),"",INDEX(CalendarItemDescriptions,J6) & " - " & INDEX(CalendarItemVendor,J6) & TEXT(INDEX(CalendarItemTimes,J6),"$#,##0.00"))

If this does not solve it, please provide a copy of your file.

The A6 reference was a type-o. I was using the most recent version and verified i was referencing J6.

i'll up load what i've got.

I adjusted the formulas in the calendar to not use column specific named ranges. As you'll see, the formula used is:

=IF(ISERROR(J6),"",INDEX(CalendarItems,J6,2) & " - " & INDEX(CalendarItems,J6,4) & " - " &TEXT(INDEX(CalendarItems,J6,7),"$###,###.00"))

This only uses the named range of CalendarItems, which is the entire table. Simply use the column number as shown in the formula.

The problem you were having is that you refered to a CalendarItemVendor, which did not exist.

thanks this worked out great.

awesome....just what I was looking for too...
thanks a bunch

This calendar is great! It is almost exactly what I need. I was wondering if there was a way to add a second date for each event. I appraise real estate and I need to keep track of not only the day I inspect the property but also the day the report is due to my client. I've been trying to figure it out for the last couple hours but I'm not familiar enough with the "INDEX" function to figure out what's going on in this spreadsheet.

Ideally I would like to have each event on the calendar twice with maybe the second time having some sort of signifier, like the cell being highlighted to indicate it is the due date and not the inspection date. I could probably figure this out if the first part was done.

Thanks for the help!
Brian