Google sheets how to create sheet that can store multiple different sheets within itself

Suppose you have a starting date in a cell somewhere (e.g. cell A1, containing 1/01/2022) - you can generate successive dates by using this formula in cell B1:

=A1+1

and copying this across. Your starting date doe not need to be fixed - it could refer to other cells which contain the month and year (i.e. the drop-down cells that you refer to). so A1 would contain the formula:

=DATE($Y$3,$z$3,1)

assuming Y3 contains the year drop-down and Z3 contains the month number. So, you now have the basis of generating dates automatically, where you can select the month and year from two drop-downs.

A calendar is a bit more complicated than that, though, as you don't want the dates to continue indefinitely to the right - instead, you want to wrap the dates onto new rows after every 7 days, so that those 7 columns can represent the days of the week. You also want to start the calendar on the 1st of the month on the appropriate day of the week. The calendar that you refer to subtracts sufficient number of days from the start point so that it actually includes the final few days of the previous month. Similarly, at the end of the month (after 6 weeks) the dates continue into the following month.

The other consideration is to pick up the appropriate data from the "events" sheet and to locate that data according to the date. Usually an INDEX/MATCH formula is used to do that.

I hope this gives you a flavour for the kind of things to take into account.

Pete

Hey! Actually I managed to figure out how to find out the day numbers and how to automate those according to other cells that contain the month, day, and year, I even managed to make a calendar, so I've got that part taken care of now, I think what I'm struggling with is how to just change the calendar to a different month just from a drop-down menu? And it's also really impressive how this calendar stores data and can switch from one month to the other... Do you have any insight on that? I'm lost

But yea I don't really know how to describe it... is this creating multiple sheets within a sheet? how is it storing the data? I'm so confused

No, it is not storing multiple sheets within the file. It merely changes the dates displayed when there is a change to the year or the month drop-downs, and then the data which is appropriate for that month changes accordingly.

Pete

But it is storing information no? if you put something into june in the calendar, switch the month to december which is an empty calendar, and then switch the month back to june, the previously stored data in june actually appears. Isn't it storing data? Not only the dates are changing, but the content within the boxes below the date too??

I think the date part of this sheet is simple enough and I was able to get it very fast! I think what I struggle with is the fact that the calendar actually is able to change between different months with its different events..

The basic data is stored in the other sheet, which is essentially a list of events on particular days - any new data would be added to this list in any order. There are formulae in the main area of the calendar (not the date part) which retrieve the relevant data for each day displayed.

The 2 main ways this can be done is by using INDEX/MATCH or VLOOKUP to bring the relevant data across to the Calendar sheet. I don't have the file open anymore, so I'm not sure exactly how this is done in the particular file that you pointed to.

Pete