Array result was not expanded because it would overwrite data in

[northcentralchoirs]

I am working to pull data from a central spreadsheet into multiple calendars tabs by using the ARRAY and FILTER formulas. The goal is to make changes/additions in the central spreadsheet and have them automatically adjust in the other tabs/calendars. I was able get this to work at first, but now it is not updated because of the error message "Array result was not expanded because it would overwrite data in (cell)."

https://docs.google.com/spreadsheets/d/1F43j7bykpjigPYrcv2uszUdjxN_8xsiMmx4EiXNob2Q/edit?usp=sharing

[GCalToolkit]

I'm not an expert on using ARRAYFORMULA (if that's what you are using) but this does seem to be a common issue with Sheets as it seems that function can't overwrite any existing data. Does the error tell you which cell is causing the problem?

I'll check your Sheet when I get to my computer later today, meanwhile these articles might help figure out what's going wrong:

https://stackoverflow.com/questions/76104398/avoiding-the-overwriting-error-google-sheets

https://copyprogramming.com/howto/error-in-google-spreadsheet-with-arrays

[northcentralchoirs]

Thank you for this! I think I have the Array and Filter formulas up and working now. If I change a date in the main spreadsheet, and it is subsequently added to the other sheets, it does not automatically click the Update box in the corresponding changed cells. Is it possible for this to do it automatically?

[GCalToolkit]

That's great - for the "Update" cells it's sadly not possible to automatically detect changes that result from data brought in from other Sheets because of restrictions on Google's end.

To work around this I've added an option to "detect changes between Sheet and Calendar" in the main menu. The same function runs when you use"Automatic Update" so they any changes coming from Formulas are flagged for update before and export.

You do need to be careful using this option if you edit both in the Sheet and the calendar and use "Export > Import" because it only detects differences between the Sheet and the Calendar, and doesn't know which is more recent... so if you edit and event in the calendar and not the Sheet, that event will be flagged for an update and the Sheet version will overwrite the Calendar version.