I was originally going to do a video explaining how to do this, but alas, technology has gotten the better of me. Instead, I figured I would walk you through how to do everything through pictures and brightly colored arrows pointing to things. If you need any help with this, please let me know! This is all relatively straight forward, but (as I just mentioned) technology can win the battle sometimes. I will still try to make a video for this, but it is not going to be a priority for now.
So what’s the point in making your own database? First off, there are so many databases out there that you can use, but often, you have to pay to download them. Some are a one-time payment, while others are monthly. Now, I’m super cheap and I don’t like spending money on stuff that will take me an afternoon to make for myself. That’s just me, but you do you! Second, when you make you OWN database, you have the option to put in exactly what YOU want. You aren’t limited to a downloadable database’s options. You also don’t feel like you have to fill out unnecessary information that the database asks for. Some downloadable databases are really cool though, and there is absolutely no problem with using one. I just prefer to make my own, and if you are like me, then I’m going to walk you through how to make one!
The video below will show you exactly WHAT we are making today. It may also help you get some ideas of what to add to your own personal database! Watch or not – it’s NOT important to watch for today’s walk through. You will also have the option to download my pre-made database for your own use. I will be uploading that and giving instructions on how to set it up in the next few days.
How to Make a Database on Google Drive
Note that the functions in this database DO NOT WORK IN EXCEL. If you have the equivalent functions to make this work in Excel too, PLEASE let me know, because I could not for the life of me get it to work correctly.
First off, if you don’t have a google account (gmail), you’ll need one for this. It’s free, and it’s very easy to sign up.
Go to drive.google.com.
New + > Google Sheets > Blank spreadsheet
Rename your spreadsheet my clicking “untitled spreadsheet” and typing in the name of your database.
Double click on “Sheet1” at the bottom of the page. This is the sub-sheet name, so call it whatever this sub-sheet will pertain to. I will name this sub-sheet “characters.”
You can also take this time to set the color you want for the sub-sheet. You don’t have to do this, but I am a color organizer and it makes me feel good about myself. I will pick a cyan color.
Fill in the row #1 with the information you’d like this sheet to have. Simply click on the cell and start typing what you want the cell’s text to be. Since this sheet is for characters, I’ve added items like name, gender, occupation, etc.
To make it look pretty and organized, I selected the row (clicked “1”), and filled the background color of the entire row’s cells. I’ll make it the same color as the sub-sheet color, just to keep consistent. I also prefer to have everything centered in each row, so I made the row’s text be centered vertically and horizontally. I also added borders to all of the cells.
The arrow on the left side of the picture shows that I dragged the dark gray line down by one row. This will freeze the whole first row, so when we scroll on the sheet, the first row will still be visible.
Since I love checkboxes, I use them a lot. To add them, highlight a cell or cells, go to the Insert tab, and click “checkbox.” For this sheet, I selected the first column (clicked “A” at the top of the sheet), ctrl+clicked the row 1 cell to deselect it (I don’t want a checkbox in the title cell), and then added the checkboxes.
Note: If you delete a cell with a checkbox, it will not uncheck the box. It will delete the checkbox all together, so be aware.
IMPORTANT
CONGRATS! Your first sub-sheet is done. Pat yourself on the back and treat yourself to a cookie. This step is crucial, so don’t forget.
Add a new sub-sheet and give it a name. I will name this next sheet “creatures.” Remember to give it a color if you want. You can add a new sub-sheet two different ways. On the bottom of the page, or through the Insert tab.
Repeat the same process as the last sub-sheet. Fill in the first row with the information you want this sub-sheet to have, and add any checkboxes necessary.
For the creatures sub-sheet, I add “food” and “venomous” columns. This will come in handy soon.
I add another sub-sheet called “plants,” and give it a few checkbox columns as well: food, medicine, and poisonous.
The Difficult Part, Which Isn’t Really That Difficult
So let me explain this first.
The next sub-sheet is going to be the “difficult” sheet, but I’m telling you…. Once you understand what is going on and how to set it up, it’s so useful! And it’s a lot easier than it looks.
The next sheet I will be making is “food.” Now, as you’ve seen, two of my sub-sheets, creatures and plants, have a checkbox column called “food.” This is not a coincidence! We are going to make it super easy on future selves. Every row that is checkboxed as “food” in either the creature or plant sub-sheets will be copied and pasted automatically into the food sub-sheet. This way, any creature or plant that I come up with on a whim, I can just click the food checkbox, and it will automatically add it to the food sub-sheet for future reference. Like this:
(Note that this is another database for this example. You didn’t miss any steps)
This is done through a function. There are some things we need to keep in mind when setting this up, but I will cover those after we go through exactly how this works. You will need to understand how this works first before I tell you what not to do.
Make a new sub-sheet, and we will name this one “Food.” However, this one will look a little different than the previous sub-sheets.
In this sub-sheet, I have mini-sheets I call lists. I have merged a few cells together just to make it look organized, and I have frozen the two rows instead of just the first. To make it look nice, I also added some borders around the mini-sheets (I just highlight the side columns and give them a side border). Since these mini-sheets are going to be automated, I don’t want to accidentally add something to them. So, I gave my future self a note to remember.
Understanding the Function
The next thing we are going to do is add the function. Now, I know this might look super scary at first, but I’ll walk you through it. We can get this together!
The function is this:
=QUERY(IMPORTRANGE(“[url of database]”, “[sub-sheetname you want to copy FROM]!A2:Z”),”select Col[#] where Col[#] = TRUE “,-1)
This is an example of what it may look like in the end (with a bunch of letters and numbers after spreadsheets/):
We want to add this function to every top cell of the mini-sheets, but every cell will need a slightly different function. Here’s what we need to add to the function:
[url of database]: This is the url from any of the sub-sheets. It doesn’t matter which sub-sheet you are on, Drive doesn’t care. All you want to do is copy and paste the url of your database into this spot.
[sub-sheetname you want to copy FROM]: For the food from plants, we want to copy from the plants sub-sheet. For the food from creatures, we want to copy from the creatures sub-sheet. So, for this example, it will either be Plants or Creatures.
A2:Z tells Drive that it wants to look at ALL the cells from Column A to Column Z, and anything below Row 2.
Select Col[#]: The number you want to put here is the column number that you want copied and pasted. For instance, if we want “Creature Name” to be copied and pasted, we want to say “Select Col4”, because “Creature Name” is in Column D, and D is the 4th letter of the alphabet. Note that this HAS to be a number, not a letter. I know, it’s kinda silly, but it will not work with a letter. It has to be a number!
Where Col[#] = TRUE: The number we want to place here is similar to the one above. But for this one, we want Drive to look through the checkboxes we have in the sub-sheet and only pay attention to the rows that are checkboxed as food (TRUE). So for this example, we want to look through Column A and find all the ones that are checkboxed. Our function will be “where Col1 = TRUE” because A is the first letter of the alphabet. Remember that this has to be a number, not a letter.
Since Row 2, 5, and 7 in the Creatues sub-sheet have checked boxes, Drive will know that we want to copy Column D from those rows and paste them into the cell where our function is on the Food sub-sheet.
I’m honestly not sure what the -1 does. My husband would know, but I don’t.
Where to Put the Functions
We want to put the functions in the top cells (below the title rows) of the Food mini-sheets. The function will copy and paste everything in the referenced column, which means that it will paste the ENTIRE column (well, the rows that are checkboxed for food that is).
When you start filling out your creature and plant sub-sheets with information, you could end up with dozens of food items in plants. Because of this, we want to make sure that EVERYTHING below the function cells is left BLANK. It will break the function if a cell is not empty below it.
Referencing our plants and creatures sheets, let’s see what the individual functions should look like.
=QUERY(IMPORTRANGE(“[url of database]”, “[sub-sheetname you want to copy FROM]!A2:Z”),”select Col[#] where Col[#] = TRUE “,-1)
So lets put these functions in their cells. To do this, we will click on the cell we want to put the function in, and then type into the fx box.
=QUERY(IMPORTRANGE(“[url of database]”, “[sub-sheetname you want to copy FROM]!A2:Z”),”select Col[#] where Col[#] = TRUE “,-1)
Note that you WILL get an error. Drive needs permission to link the sheets together. To do this, hover over the box with the #REF error and click on “allow access”.
Also note that you will get an error if you don’t have any boxes checked in the other sub-sheets. Even if you do get errors, it doesn’t mean you are doing something wrong!
Let’s put our work to the test. Write in some information in the Plant and Creatures sub-sheets.
It seems to be working great, so I will fill in the rest of the cells with their respected functions and test them out.
Another note: it will take Drive a few seconds, even up to a minute or so, to update the Food sub-sheet. Don’t worry if it doesn’t update right away.
Now, I will add in a few more rows of food plant and creature items in the plant and creature sub-sheets, just to make sure that they will show up in our list. We also want to make sure that things that aren’t supposed to show up, don’t.
And it’s working perfectly!
For future mini-sheets, remember that you may need the function to say “where Col2 = TRUE” instead of Col1. For my medicine mini-sheets, that’s exactly what I needed to do.
Now that we know how to do this, we can set up as many lists and sub-sheets as we want. If you haven’t watched the video I posted above and you need inspiration for what kind of things to add to your database, give the video a watch! I also have a blog post here that has a few pictures of a database I’ve already made in case you don’t want to watch the video.
Once this is all set up, do not move the columns in the plant or creatures sub-sheets. If you do, you will have to edit your function to look at the new columns.
And that’s really all you need to make your own personal database. It’s so simple once you get the hang of it, but it is so effective! You can get this all set up in the course of an afternoon, but it will come in handy for years for your project.
If you have any questions, or if your database isn’t working the way you want it to, please let me know and I will try my best to help! Know that I’m not a professional when it comes to this kind of stuff, so I may not be able to help with very complex things. This is really just a starting point, and it’s up to you to be creative with your information.
I hope this helps all of you! If any part of this is overly confusing, please let me know and I will try to explain it better in a different way.
I will be uploading my pre-made database for you to download and use. I’ll have instructions on how to set it up as well. Expect the database to be uploaded sometime this week.
melissabeenewton.com 