[Solved] Multiple holidays on a calendar day

[Fitch]

Hi everybody.
I decided to make a perpetual calendar by plagiarising the best of other calendars.
I'm not an expert on Calc, but I managed with the forum help to get something reasonable. Thanks.
My last little problem is, that if there are two events on the same day, it only shows the first one.
e.g. in 2016, Burns Night and Bubblewrap Awareness Day both fall on the 25th January.
It only shows Burns Night in the January sheet.
I have (hopefully) attached the spreadsheet for everyone's perusal.
All the dates you want printed in the month sheets go into the right hand column in the workings sheet, and hey Presto!
Well, that was the idea, anyway.
I tried to ladle it with as many instructions so:
a) I can remember how to put in the dates
b) it can be put into a "Handy Spreadsheet" site for everyone else. Once I get that last bit right... Someone might also put me in the right direction for such a site.

calendar.ods

(97.65 KiB) Downloaded 194 times

[RoryOF]

Place cursor where you want the break, then Ctrl Enter

[Fitch]

Really?

[ken johnson]

Also, if you need the two events to be visible in the table in columns AI and AJ of the Workings sheet you could use merged cells as per attachment...

Ken Johnson

[Fitch]

Aah!
I see what you mean...

So now lets look at the year 2017:
Burns Night is on the 25th Jan as per normal.
Bubblewrap Awareness Day is on the 30th Jan.

In 2018, Bubblewrap Awareness Day is on the 29th Jan.
And what's more, Palm Sunday and British Summertime both fall on the 25th March.
Just as BST and Easter Sunday fell on the same day in 2016 (27th March).

I always knew the problem was in the "day" cells in each month.
e.g. =IF(ISNA(VLOOKUP(M8,Holidays,2,0)),"",VLOOKUP(M8,Holidays,2,0))

[Fitch]

Anyone?

I tried, e.g:

Code: Select all

=IF(ISNA(VLOOKUP(D12,Holidays,2,0)),"",VLOOKUP(D12,Holidays,2,0) &CHAR(10) & IF(VLOOKUP(D12,Holidays,2,1)<>VLOOKUP(D12,Holidays,2,0),VLOOKUP(D12,Holidays,2,1) ,""))

Now I get too many Global Belly Laugh days and the wrong day for Bubblewrap Awareness day as well as the correct holidays.

The days in the month of January have been altered with the above formula to see the problem.

calendar.ods

(98.56 KiB) Downloaded 183 times

[ken johnson]

Provided Holidays Database Range contains no more than two entries sharing the same date you can lookup the second entry by changing the height of vlookup's second parameter, array.
For example, 25/01/16's first entry, Burns Night, is on Holidays' 6th row. Hence VLOOKUP's array parameter for looking up 25/01/16's second entry, Bubble Wrap Awareness, should start on Holidays' 7th row,the next row down.
This reduced part of the Holidays Database Range is returned by the following formula...

Code: Select all

INDEX(Holidays;MATCH(C12;INDEX(Holidays;0;1);0)+1;1):INDEX(Holidays;ROWS(Holidays);2)

Note that it has two INDEX functions separated by a colon. When the INDEX function is used this way each INDEX function returns a reference to a range instead of its contents.

Code: Select all

INDEX(Holidays;MATCH(C12;INDEX(Holidays;0;1);0)+1;1)

returns a reference to the cell immediately below the first instance of 25/01/16.

Code: Select all

INDEX(Holidays;ROWS(Holidays);2)

returns a reference to Holidays' bottom-right cell.
Using this method in your formula results in...

Code: Select all

=IF(ISNA(VLOOKUP(C12;Holidays;2;0));"";VLOOKUP(C12;Holidays;2;0) &IF(ISNA(VLOOKUP(C12;INDEX(Holidays;MATCH(C12;INDEX(Holidays;0;1);0)+1;1):INDEX(Holidays;ROWS(Holidays);2);2;0));"";CHAR(10)&VLOOKUP(C12;INDEX(Holidays;MATCH(C12;INDEX(Holidays;0;1);0)+1;1):INDEX(Holidays;ROWS(Holidays);2);2;0)))

Ken Johnson

[Fitch]

Well, that certainly seems to work.
I noticed you managed to do all 365 days.
That must've taken ages, or do you have a sneaky way?...
Thanks!
Is there a site I can put it for people to share?

[ken johnson]

I noticed you managed to do all 365 days.
That must've taken ages, or do you have a sneaky way?...

Nothing sneaky, just Copy then Paste Special/Formula.

Is there a site I can put it for people to share?

Any file sharing site could be used.
I mainly use MediaFire.

Ken Johnson

[gilhol]

How do I expand the Holidays Database Range. I have 145 relative's birthdays I want to add.

[MrProgrammer]

How do I expand the Holidays Database Range.

Data → Define Range

If you have further questions, open your own topic. This one belongs to Fitch. You are more likely to get your problem solved quickly if you attach the spreadsheet file you're working with. Do not attach a picture of it.