[SOLVED] Streamlining an array formula (calendar)

Wow! That is slow - I inserted a column in your data table and it took minutes to give control back to me, I then put a formula in that column, and again it took minutes, so it will take me too long to revise all the formulae.

I would suggest that you work with proper dates (as Excel recognises them), because you can easily format them to show only days, or long-form months, and you can use the TEXT function to do this. Generally, in my calendars I use a helper column in the Events sheet which combines the date with a unique sequential number derived from the date, so that multiple occurrences on the same date can easily be identified. There is no need, then, for array formulae in the Calendar sheet, as a simple INDEX/MATCH formula can display the relevant information. Here's a thread where I posted a calendar file yesterday (in Post #7, although there are references to two others in earlier posts):

https://www.excelforum.com/excel-for...end-dates.html

This displays a monthly calendar of events, where the month and year can be selected from drop-downs. It can display up to 10 events per day in groups of 10, i.e. 1-10, 11-20 etc., so in effect it can cope with up to 100 events per day. You can decide to display one of three fields of data, or all of them, and for events covering several days (or months) you can just set a start and end date range in the Data tab, and this will be expanded automatically. It responds immediately to any changes as it is purely formula-driven, and is very fast,

Take a look at that and see if you can pick up some ideas on how to improve the performance of your workbook.

Hope this helps.

Pete

Thanks for the rep.

Presumably you found something of interest in those files?

Pete

Hi Pete,

Looks like my last comment didn't post. Thanks very much for your help - definitely useful - I'm taking some ideas to vastly improve my calendar!

Hi Pete - I wonder if you can help me with editing a formula of yours?

In the attached, on 'Public Calendar View' in cell B11 (and B17, B23, B29), I've edited a formula that's returning the wrong value. (I've added the "No Event" comment just so it's easier to see - I'll delete this after.) For A11='Data View'!E:E it's returning a date reference that's incorrect each time (I've noted how incorrect in column I) and I can't figure out why.

Are you able to help?

Well, the formula that you had is an array formula, as you are looking at two different columns in the Data View sheet to look for matches (and using full-column references, which is a NO-NO for array formulae). If you really want to, you can do CTRL-SHIFT-ENTER on that cell (i.e. B11), and after a short delay you will get the result Ice Rink.

However, this approach is going to slow things down, so you can make better use of the helper column in the Data View sheet to improve things. Change the formula in E2 of the Data View sheet to this:

=EventData1[[#This Row],[Date]]&"_"&EventData1[[#This Row],[Ref]]&IF(EventData1[[#This Row],[Public/Private]]="Public","_P","")

You can see that it adds a "_P" to the end of the of the result if you have Public in column P. Then in B11 of the Public Calendar sheet, the formula becomes:

=IFERROR(INDEX('Data View'!$F:$F,MATCH(B$10&"_"&$A11&"_P",'Data View'!$E:$E,0)),"No event")

Note that I've removed the sheet references, as you don't need them if the formula refers to the same sheet. This will be a lot faster than the array formula. If you want to have an "Admin Calendar View" sheet, where all events are listed, then you would replace the &"_P" shown in red above with &"*", which means "any (or no) characters" after the date_number part.

Hope this helps.

Pete

I see! I was trying not to use full column references but for some reason I kept encountering incorrect values when I tried it so I gave up.

This makes a lot of sense - think I've learnt a lot from this one thread, so thanks!

You're welcome - come back any time.

Pete