Populating a calendar with bills/events based on monthly/semiannually/bimonthly

[Svet]

Hi guys
Ive searched through all the calendar related threads and couldn't find one that would match what I require, so I started a new thread.

I would like to populate a calendar with my data but depending on monthly/bimonthly/fortnightly/semianually and so on intervals.

If my data shows fortnightly, I would like the event/bill to show on the calendar every second week, and bimonthly every other month, semiannually every 6 months.

I hope you could help

File uploaded

 

[Hui]

Have you had a look at this calendar?
http://www.excelhero.com/blog/2012/01/excel-universal-calendar-template.html

 

[Svet]

Hi fellow West Aussie
Thanks for that, I'll check it in the morning (night shift).

 

[Svet]

Have you had a look at this calendar?
http://www.excelhero.com/blog/2012/01/excel-universal-calendar-template.html

Hi Hui,
The calendar is awesome,"but", it still doesn't cut it for what I require.
It's also restricted to one event a day where I will like to display a number of events, you would have to manually type in dates for events monthly, bi-monthly etc.
What I would like is to type in one date and that to be calculated by the rate it should show up on the calendar.
If help columns and tables are required, what would be the best way to tackle this query.

Kind regards
Svet

 

[NARAYANK991]

Hi Svet ,

See the attached file ; you can run the Populate_Calendar macro , and see if it works ; if it does , copy the macro and paste it in your file.

I have converted the frequencies to a table , which you will have to do in your file.

Narayan

 

[Svet]

Hi Svet ,

See the attached file ; you can run the Populate_Calendar macro , and see if it works ; if it does , copy the macro and paste it in your file.

I have converted the frequencies to a table , which you will have to do in your file.

Narayan

Hi Narayan,
That did the job, but I want to keep it flexible and I was trying to avoid VBA (it's not my strongest), if there is another solution with formulas and tables, it would be prefered, I'll keep your macro with your permission just in case.

Thanks for your efforts

 

[NARAYANK991]

Hi ,

I doubt that formulae alone will do the job , especially because copying the formulae to different cells and different sheets is itself a big task !

Secondly , the fact that with multiple events possible on any one day , putting the event in the first vacant slot is something I doubt formulae will be able to take care of.

Let us wait and see if someone else comes up with this.

Narayan

 

[Hui]

I concur with Narrayan, that a formula based solution is unlikely

 

[Svet]

I concur with Narrayan, that a formula based solution is unlikely

 

[Svet]

Can either one of you maybe help with a formula that will extract data in a table that matches a criteria and gives me a unique list from that column.

So, the table is $B$38:$AF$60 including headers which are dates of the month.
The criteria will be the date on the calendar and under the date will be the formula required that matches the date in the table and then gives me the unique list of that column.

I don't know maybe something like this.
=IFERROR(IF(HLOOKUP($AM$37,$B$38:$AF$60,INDEX($B$38:$AF$60, MATCH($AM$37,))),INDEX($B$38:$AF$60, MATCH(0,IF(ISBLANK($B$38:$AF$60),1,COUNTIF($AN$38:AN38, $B$38:$AF$60)), 0))),"")
It came up blank with no errors. I have no idea what I am doing but I'm giving it a good crack..

 

[Somendra Misra]

Hi Svet,

If your above question is not related to your original query, than you may upload a sample file to illustrate what you are writing.

Regards,

 

[NARAYANK991]

Hi ,

Try this array formula , to be entered using CTRL SHIFT ENTER :

=IF(ISNA(MATCH($AM$37, $B$38:$AF$38, 0)),"",INDEX(INDEX($B$39:$AF$60,0,MATCH($AM$37, $B$38:$AF$38, 0)),MATCH(0,COUNTIF($AN$38:$AN38,INDEX($B$39:$AF$60,0,MATCH($AM$37, $B$38:$AF$38, 0)) ),0)))

The assumptions are :

The date criterion is in AM37.

The formula is to be entered in AN39 , and copied down.

I have not used an IFERROR ; you can see if it is required , which it may depending on whether data is present or not.

Narayan

 

[Svet]

Hi Svet,

If your above question is not related to your original query, than you may upload a sample file to illustrate what you are writing.

Regards,

Hi Somendra
Thanks for the prompt response, to answer your question, it is related to my original post, I'm just trying to crack this myself with no luck, I just need that formula to work and it should be sweet.

File attached...thanks in advance

 

[Svet]

Hi ,

Try this array formula , to be entered using CTRL SHIFT ENTER :

=IF(ISNA(MATCH($AM$37, $B$38:$AF$38, 0)),"",INDEX(INDEX($B$39:$AF$60,0,MATCH($AM$37, $B$38:$AF$38, 0)),MATCH(0,COUNTIF($AN$38:$AN38,INDEX($B$39:$AF$60,0,MATCH($AM$37, $B$38:$AF$38, 0)) ),0)))

The assumptions are :

The date criterion is in AM37.

The formula is to be entered in AN39 , and copied down.

I have not used an IFERROR ; you can see if it is required , which it may depending on whether data is present or not.

Narayan

Hi Narayan
Well it worked to a degree, but the list was not unique, it had the same result in the whole column.

 

[Somendra Misra]

@Svet

I am little confused looking at your file. Can you explain where you are running this formula and what unique list you want and based on what?

Regards,

 

[Svet]

Hi Narayan
Well it worked to a degree, but the list was not unique, it had the same result in the whole column.

My appologies Narayan

I placed the formula in AM39
It worked fine, would iferror work in place of ISNA?

 

[NARAYANK991]

Hi ,

Yes , but I suggest that you place the IFERROR around the entire formula , as it has been posted , so that you will end up with :

=IFERROR( postedformula , "" )

which will result in blanks if the posted formula returns an error value.

Narayan

 

[Svet]

@Svet

I am little confused looking at your file. Can you explain where you are running this formula and what unique list you want and based on what?

Regards,

Hi Somendra
I would like to populate the yellow fill with red border cells to the right, I have a comment attached to it.

Extract the data from $B$38:$AF$38 for this excercise.
Thanks

 

[Svet]

Hi ,

Yes , but I suggest that you place the IFERROR around the entire formula , as it has been posted , so that you will end up with :

=IFERROR( postedformula , "" )

which will result in blanks if the posted formula returns an error value.

Narayan

Narayan, you are a legend,
Many thanks, I'll upload the calendar once I have cleaned it up a bit, then I'll ask if we can streamline it a little.

 

[Somendra Misra]

@Svet

I don't see the possibility of duplicate in your table B39:AF60, if so you can use below formula in AN39 and copy down with criteria date in AM37.

=INDEX(INDEX($B$39:$AF$60,,MATCH($AM$37,$B$38:$AF$38,0)),SMALL(IF(INDEX($B$39:$AF$60,,MATCH($AM$37,$B$38:$AF$38,0))<>"",ROW($B$39:$B$60)-ROW($B$39)+1),ROWS(AN$39:AN39)))

Confirm with Ctrl+Shift+Enter.

Regards,

 

[Svet]

@Svet

I don't see the possibility of duplicate in your table B39:AF60, if so you can use below formula in AN39 and copy down with criteria date in AM37.

=INDEX(INDEX($B$39:$AF$60,,MATCH($AM$37,$B$38:$AF$38,0)),SMALL(IF(INDEX($B$39:$AF$60,,MATCH($AM$37,$B$38:$AF$38,0))<>"",ROW($B$39:$B$60)-ROW($B$39)+1),ROWS(AN$39:AN39)))

Confirm with Ctrl+Shift+Enter.

Regards,

Wow, that works a treat too , I just added =IFERROR( postedformula , "" ) as Narayan suggested....thank you to the both of you, I am getting something out of this and am grateful.