How to move certain range between worksheets with Indirect function?

Welcome to the forum!

I think your approach complicates things more than it needs to. You basically have two display sheets (per month) and no event list, and you're trying to get the display sheets to feed off of each other. I would recommend creating an event log (just one for the year) and having all display sheets pull their data from there. In the attachment, I've created a sheet called "Event List", where you can list your events in no particular order as they are planned. The date you enter in column A of the event list will feed that entry to both the "Year" and "May" sheets. If you need to reschedule an event, just change the date in column A of the Event List and the event should be repositioned on both "Year" and "May".

For "Year", I used the following formula in A3. It should be array entered (confirmed with Ctrl + Shift + Enter instead of Enter):

=IFERROR(INDEX('Event List'!$C$2:$C$40,SMALL(IF((TEXT('Event List'!$A$2:$A$40,"mmmm")=$A$1)*(DAY('Event List'!$A$2:$A$40)=C3),ROW('Event List'!$A$2:$A$40)-ROW('Event List'!$A$2)+1),ROW(1:1))),"")

Fill it down to A5, then copy and paste formulas for the other days in that week. Repeat for the other weeks, ensuring that the last function in the formula reads ROW(1:1) for the start of each new week. That tweak should be clearer in the attachment.

For the "May" sheet, I left your INDIRECT formula in place for column C, then used the following in D3 (non-array):

=IFERROR(INDEX('Event List'!D$2:D$40,MATCH($C3,'Event List'!$C$2:$C$40,0)),"")

Fill right and down. In all formulas in the attachment, I restricted the range to row 40 of the event list. If you're planning to have more than 40 events per year, just increase the "40"s in the formula to something bigger. Once this setup is in place, the only sheet you should ever need to enter info into or adjust will be the "Event List" sheet. Give it a look:

Without overcomplicating things, it shouldn't be too tricky to add months. For a new calendar sheet, just copy over the May sheet, change A1 to 'June', and tweak your dates as necessary. I'm hoping you took the calendar sheets from an available template, so that the days/dates are already coordinated, but if not, it only takes a minute or two to re-number the dates. For a new month's sheet like the one called 'May' in your attachment, just build it the same way you built 'May' and use the same formula in D3, filled right and down. I added a shortcut in the attached "June" sheet that turns A1 into a date, which enables you to use a formula in B3, filled down, to return the day of the week:

=IF(ISNUMBER(A3),TEXT(DATE(2018,MONTH($A$1),A3),"dddd"),"")

In column C, a few minutes of some combination of grunt work and careful 'replace all' swaps should help you update your INDIRECT formula. See the attached for a look at June:

Thanks Cantosh,
What I actually meant was if it would be possible to add June calendar in a Year worksheet and only have new Month worksheet for the details.
Sorry for all the fuss

I've attached the file for the reference.

Your approach is definitely something that works for me

Originally Posted by CountDom

Would [it] be possible to add June calendar in a Year worksheet and only have new Month worksheet for the details.

Definitely possible - the idea is the same. Update the formulas from May --> June. The 'Replace All' option makes it pretty easy to key all of the June calendar cells to the new month in A19, and you'll also want to be mindful to update each cell to start with ROW(1:1) as the ROW term in the formula. Tweak the 'June' sheet's INDIRECT formula to pull from the new June calendar range. Replace All proves useful here, as well. Take a look at the attachment for a bit more clarity: