LinkBack URL
About LinkBacks
Hello,
Is there a way to have up to 5 events listed on the calendar for each day with formulas?
Thank you very much for your help
Hello,
Is there a way to have up to 5 events listed on the calendar for each day with formulas?
Thank you very much for your help
Try this:
=IFERROR(FILTER(Events!$D$5:$D$41,Events!$G$5:$G$41=Calendar!K10),"")
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
G5, copy pasted into the other cells below the day number:
=IF(G4="","",IFERROR(FILTER(Table3[Special Occasions],Table3[Date]=Calendar!G4),""))
I've done it for the first two weeks ONLY.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Thank you both for your help. How do I have formulas in the other calendar cells?
What do you mean?
I don't know how the filter works for the other cells in each day calendar region. I was able to copy each set of rows from Glenn's version to cover all of the calendar cells.
I see how to copy the first formula but the other formulas for filtering get grayed out.
That's becase it's a SPILL formula and copies itself.
How does the spill know how far to go? I am not familiar with how this works very sorry for being trouble.
The spill formula resides ONLY in the cell in which it is entered. All the calculations are performed in a SINGLE cell. This makes them very efficient (normally..). However, if multiple results are returned the results spill down the column (in this case) or across columns (or both) depending on the data. They're greyed to remind you that they are not actually being GENERATED in that cell.
It will go as far as there are items in the filter for that day. You said it would be up to 5: if there are 4, it will fill 4 rows, and so on.
Oh okay I get it. Thank you both for the valuable education.
No worries - this is why they are called Dynamic Array formulae.
@AliGW
=IFERROR(FILTER(Events!$D$5:$D$41,Events!$G$5:$G$41=Calendar!K10),"")
I tried the above it works, but it will appear ### when no date. How can i allow ### appearing?
Administrative Note:
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please see Forum Rule #4 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks