+ Reply to Thread
Results 1 to 7 of 7

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

  1. #1
    Registered User
    Join Date
    07-17-2022
    Location
    Canada
    MS-Off Ver
    2019
    Posts
    4

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

    Hi guys,

    I recently stumbled upon a free template online, where the author created a calendar that was able to change the whole calendar on a sheet based on a drop-down menu. It's really hard to explain, so I'll just link it here: (I can't quite link because I'm too new so I'm gonna just write the URL down here as text, and not a link, sorry for the inconvenience)

    PART 1 of link:docs.google.com/spreadsheets/d/1AiT

    PART 2 of link: KsaWLfKlzm5V-pa_7UuL0zA9Dk4SpVCTL6jKXUbU/edit?usp=sharing


    The sheet that demonstrates what I'm talking about is the sheet titled "Calendar" and you can see it happen when you change the month in the drop-down menu on the right side of the sheet. (column P i think). EDIT: what's impressive is that the calendar is able to store data in each month. Like if i put an event into june, and I switch the month to december and see a blank calendar, and then switch back to june, my event reappears!

    I really wanted to learn more about this sheet because of the data storing (???) that it seems to do, as I want to make my own calendar for my gym progress diary, and it honestly is just fascinating in general, and I'd love to learn more about this. If anyone could help point me toward a resource or tell me more about how to do this kind of thing in google sheets, I'd really appreciate it.
    Last edited by pandatime; 07-17-2022 at 03:44 PM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Google sheets how to create sheet that can store multiple different sheets within itse

    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

  3. #3
    Registered User
    Join Date
    07-17-2022
    Location
    Canada
    MS-Off Ver
    2019
    Posts
    4

    Re: Google sheets how to create sheet that can store multiple different sheets within itse

    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

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Google sheets how to create sheet that can store multiple different sheets within itse

    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

  5. #5
    Registered User
    Join Date
    07-17-2022
    Location
    Canada
    MS-Off Ver
    2019
    Posts
    4

    Re: Google sheets how to create sheet that can store multiple different sheets within itse

    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??

  6. #6
    Registered User
    Join Date
    07-17-2022
    Location
    Canada
    MS-Off Ver
    2019
    Posts
    4

    Re: Google sheets how to create sheet that can store multiple different sheets within itse

    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..

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Google sheets how to create sheet that can store multiple different sheets within itse

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Google Sheets: Create an IF statement with a subtraction, multiple times. Display Result
    By mlinds1989 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 6
    Last Post: 02-20-2021, 06:06 PM
  2. Replies: 5
    Last Post: 02-20-2021, 12:48 AM
  3. [SOLVED] Google Sheets: Update slave/child sheets from Master/Parent sheet
    By JGREEN17 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 01-29-2021, 03:03 PM
  4. Google Sheets: Create a new Sheet on Each Row
    By abdullahsikandar in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 2
    Last Post: 01-27-2021, 01:05 PM
  5. Google Sheets Master Sheet that pulls in data from other sheets with the last edit date
    By Badvgood in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 2
    Last Post: 12-19-2020, 12:04 PM
  6. countif from multiple sheet (In google sheets)
    By wlinksanju in forum Excel General
    Replies: 1
    Last Post: 09-29-2020, 04:24 PM
  7. Replies: 1
    Last Post: 01-25-2014, 02:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1