How to build auto-fill calendar on Excel?

Hey guys,

Was wondering if I could get some help on this one. I'm looking to create an auto-updating Excel file for a full year (hopefully has the ability to do multi-year as well) for each month that is in Calendar form. Basically its pulling in the information to the right and populating it into the Excel on the right. I'm not an Excel expert, so I can't seem to figure out how to get Excel to return multiple data for one lookup source.

I'm hoping to create one where I can potentially toggle the month / year or even one with the ability to switch between two months (for example Jan + Feb or Aug + Sept) would be good as well.

Appreciate all the help!

[ATTACH]415850

The following thread was the first time I submitted a calendar file to the forum:

http://www.excelforum.com/excel-new-...readsheet.html

The attachment in Post #4 seems to do most of what you want.

Hope this helps.

Pete

Hi Pete,

Thanks for the response! This is what I was looking for! One question, for the dates, is it possible to turn them into a mm/dd/yyyy format instead of dd/mm/yyyy format? I tried entering mm/dd/yyyy into the activities chart but its returning an error for me.

Thanks!

Select column B in the Activities sheet by clicking on the B at the top of the column, then right-click and choose Format Cells and on the Number tab choose Date rather than Custom and select the style that you require. Click OK to exit.

Hope this helps.

Pete

Hi Pete,

One last question. Is there way to extend the number of rows for each date? I am looking to have around 14 rows per date if that's possible.

Thanks again!

It's possible, but more involved than a simple explanation here can describe.

If you post a sample workbook showing your own activities, with examples that need up to 14 rows, then I can modify the calendar for you and test it out. It will be later this evening, as I'm going out now.

Pete

Hi Pete,

Won't be able to post exactly what I need, but I did a simple example of what it looks like on Excel.

I think its just an extension of the document you posted earlier with the auto-fill calendar, as I just need it to return the detail for each corresponding date.

The actual document has about 60 rows in total so far (so even 10-12 rows would be good as well in the calendar), but has the potential to increase further.

Multicalendar example.xlsx

I really appreciate all your help, you've been a lifesaver so far!

Best,

I've incorporated your sample data into the Projects sheet in the calendar file (attached) and put a formula in column A which I've copied down to A1000. If you think this is far enough, then you can hide this column.

On the Calendar sheet itself, I've increased the number of display lines for each day to 10, but I've also included a facility whereby you can change the range of display in groups of 10 (e.g. from 1 - 10, or 11 - 20, and so on), using a new drop-down in cell M5. You might need to widen the columns further to see all of the detailed text.

Note that you don't have to enter the data strictly in sequential order on the Projects sheet, as the formulae will pick up on the date and put the entry in the correct place on the calendar - if you now put a new entry for 5th July at the bottom of your list, you will find that it will be picked up as the 14th entry for that day.

Hope this helps.

Pete

Hi Pete,

Looks great! Can't thank you enough for this!

All the best,

RazedC

Glad to hear that.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete

Pete_UK -

I've also found this spreadsheet very useful, can you tell me how to extend the calendar beyond 2020?

Also, is there an easy way to show more than 10 entries per date without having to switch the "range" drop down menu?

Apologies if I should start a new thread - Thanks in advance!

Yes, you should not try to hijack someone else's thread (see Forum Rules at the top of the screen). Instead, you should start your own thread, with a link back to this one if you thing it is relevant.

Hope this helps.

Pete