Topics > Member Questions

49 Tutorials

Better Sheets Member Questions
Questions directly from members answered, for you.

Watch this video to learn how to ask a question you have.

Get Sheets Help

These are questions about Google Sheets from members of Better Sheets. Questions directly from members answered, for you. The point of these videos is that one person had a problem, got the solution but also all Better Sheets members can now learn from that one video. 

While these are questions FROM Better Sheets members they are all answered by Andrew Kamphey, the resident Google Sheets wizard at BetterSheets.co and you'll get that response from him every time. There's no forum (yet) on Better Sheets. There are searchable directory of Formulas, Tutorials, Templates, and Tools.

If you're not yet a member, and you want to ask a question, there are videos on how to best ask a question. It's best to make a question that is specific but not TOO specific. Not vague, and can be shown. Meaning you have a 1 minute loom recording, or a sheet to share. Sharing a sheet with fungible information is best so I can make a video response. 

Don't share private data. Make sure the sheet is abstract enough to get your answer but not specific enough to have any revealing private data.

49Tutorials

the answer is if you have a column of true or false you can use the count if statement argument formula. That's just count if, and then you say your range, and then comma, true. Or false, whichever one you want and you'll get a count.
You're joining a company and they have some Google Sheets already created, and there's a sheet that has a cell in A1, this number, and you want to iterate it to 3, and B1 is connected to a lot of other stuff, so you don't want to mess with it. All you want to do is iterate a one, one at a time. I think the answer here is an Apps Script. 
Create easy to use math iterators with the ROW() formula. It tells you the row your cell is on, but why do you need that? you can iterate math equations! It's neat!
Are you wondering if you can sell a google sheet and only allow buyers to use the sheet, and not share it? I answer that sort of question, with more questions! 
This tutorial showcases a practical solution to convert visual cues into actionable data in Google Sheets. It's a step-by-step guide on using Google Apps Script to automate the process of reading background colors and converting them into text. The focus is on simplifying data processing tasks and enhancing the utility of spreadsheets for data analysis.
Create relational databases with index/match. Discover the secret to efficient data management with primary keys! Learn how to sort, filter, and create tables based on unique identifiers in this expert video. Say goodbye to complicated spreadsheets and hello to easy data management.
Learn the Quick Fix! Replace Text with Blank Cell in Google Sheets Hassle-Free. Say Goodbye to Data Glitches. Super helpful when you need to find and replace in google sheets any text.
Your question is, you have these events and dates and statuses. And you want to turn them green actually you wanna turn them any, some color, the whole entire row based on if in this D column the status is closed.
Find out in this quick experiment if a tab name can be alerted to the owner of the spreadsheet. Great for owners and operators who want to know if someone is changing the sheet.
Create a prompt to OpenAI with an array of cells
How to create a form programmatically using Google Apps Script. I show you step-by-step how to create a form, set the title, add text items, and set the destination of the form to a specific sheet. This tutorial is perfect for anyone who wants to automate the process of creating forms, whether it's for templates, lead magnets, or recurring forms. No prior coding experience is required.
Sync more than two tabs with Apps Script
Automatically copy a spreadsheet tab to a whole new spreadsheet file, get the url of that new sheet, and make sure the new file is values only. no formulas. A member asked this question and I'm happy to answer it, code it, and let you in on the answer in this short video.
The video tutorial is about tagging and creating comma-separated lists of tags for a list of titles. The tutorial demonstrates how to use data validation to create a drop-down menu of tags for each title and how to keep track of the number of videos or items in each tag category. The tutorial also shows how to create a comma-separated list of tags for each title using the JOIN function.
Jodi asked:  ab out Building a sales CRM spreadsheet that has 4-5 tabs at the bottom. Each sales person gets their own sheet and then I want a master spreadsheet that pulls each salespersons Information they enter (customer, job, status etc) into one place. 

I’m not sure how to get the master sheet to pull in from the other sheets as they enter the data so that the master sheet shows real-time what’s happening without making one row = a row from another sheet.  I just want it to come in one row after the next as it happens.

Other Videos Mentioned in this video:
https://bettersheets.co/tutorials/add-a-timestamp-to-task-lists-without-now-formula
https://bettersheets.co/tutorials/spreadsheet-automation-101-lesson-2-onedit-trigger

Spreadsheet Automation 101: https://bettersheets.co/courses/spreadsheet-automation-101
Create multiple functions that run with the result of other functions.
Find the first value in a row.
Make your VLOOKUP formulas flexible and your data move-able with this trick.
I'll show you how to easily find a specific value in a range using two powerful formulas: VLOOKUP and INDEX MATCH. 

With VLOOKUP, we can search for a value in a column and retrieve information from the same row. 

I'll explain how to set it up and why the "false" parameter is important. Then, I'll introduce INDEX MATCH, which allows us to search for a value in one column and retrieve information from another column.

 I'll demonstrate how to use it to find a name based on a number and vice versa. This technique is incredibly useful when you need to look up information based on a specific value. 

Plus, I'll share a neat trick to make the search even more dynamic. So, let's dive in and master these versatile formulas together! 🚀
If you have a list of tasks and statuses, the FILTER formula will help keep you from the brink of Overload by allowing you see each status in a different tab.

Great if you're assigning work to VAs or you just became overloaded with tasks in your own task management system.

A great alternative to Conditional Formatting as well.

And a sneaky new little Apps Script that saves the day.
I've created simple automations to make an awesome automatic task list. Add a checkbox when adding a task. Add a timestamp when done. Also emailing every day the top task.
Go through a fun sheet and make it better.
She has a cell, this D one, in which it says, for instance, another array or a row or column in this case, a column, a on sheet one where we're on and she's referencing that from another cell.
What is "&" doing in this formula? MONTH(A1&1)
Do you know why this is so cool? It can replace an entire vlookup chart I've been making for years.
Use arrayformula to deal with Google form entries on a sheet. Even as they keep getting added.
Find out how to get a formula to get a row, but get it multiple times per row.
Got this question in the Facebook Group: How to count the number of numbers in a row. I saw two possible solutions mentioned in the comments and wanted to go through why those are okay but there is a much more flexible and better solution.
Pick a raffle winner if you only have the names and number of tickets. And do it in one cell.
Find out how to automatically delete rows based on a cell's value. We do this in Apps Script
How to figure out a percentage score when our reporting numbers are a formatted in a particular way. In this case we have a score and then a slash and then the total possible score in a single cell. We go through each option of solution and try to derive a solution that is both useful in the exact situation and possibly useful for other problems too.

We find that the Query formula and other solutions are inflexible or time intensive. We want a solution that does actually solve the problem at hand, in a quick way, and also is easy to edit and flexible to solve other problems.
how would I do the following? In Google Sheets, it's in column A I have a list of URLs. For each of those URLs, I want to pull that URL's featured image and meta description, and put those in columns B and C. Here's how you do it. I just grabbed Wikipedia articles for this example.
We go deep into the syntax and usage of the SPLIT() formula.
Search through and filter data to get what you want, when you want it. Tired of sifting through endless rows and columns? Learn how to filter your Google Sheet database with dates, checkboxes, and dropdowns. Simplify your workflow today!
We have a question from Rupesh about the quick CRM. He wants to add some team members.
Useful for calculation or filters by dates.
ow do I show the date and time for the last time something was changed in a sheet? So what's interesting is that this This shows you Google Sheets shows an version history up here and shows you the last edit, but sometimes we want to have that data available to us inside of the sheet.
find out how to show the total number of data rows available in a sheet.
A member, Alejandro, asks about taking their custom calendar inputs and figuring out study totals for a week. They created a beautiful input and just need to the calculations. I show how to use the data to do the calculations. If you've never used the FLATTEN() formula, this is a good example! And we use double UNIQUE().
Member Ben asks how to add 1 month to a date.
Got a list of names, need to add new ones to main list.
In Excel you actually have these options of sorting by year, by month, by date. But let's do it in Google Sheets.
Create a countdown timer inside of a google sheet. Unlock the secret to a lifetime deal... TIMEI! Discover their amazing live Google sheet and join the countdown to their exclusive offer. Watch as we recreate and enhance their sheet with stunning colors. Don't miss out on this epic journey. Click now!
Member Richard asks this question. Answer: use Google Script and a Trigger.
So in this video we're going to compare two tabs and differences. Basically, someone asked actually on AppSumo, someone asked how do I compare two sheets to know what the differences are? This is the answer.
One common feature that users often request is the ability to add star ratings to their spreadsheets. While this feature is not built into Google Sheets, it is possible to create a star rating system using emojis and data validation.
This video is a tutorial on how to create a dynamic weekly planner in Google Sheets with drop-down menus that change images based on selections. Here are three key takeaways:

Avoid Hidden Rows/Columns for Clarity

The speaker advises against using hidden rows or columns in Google Sheets. They suggest that this practice often leads to confusion and inefficiency, especially when sharing sheets with others. As an alternative, they recommend placing such data on a separate sheet.

Dynamic Dropdown Lists and Image Insertion

The tutorial demonstrates how to create dropdown lists where selecting a subject (e.g., English, Math, Study) changes the image displayed in another cell. This is achieved through data validation for dropdowns and using VLOOKUP or INDEX MATCH functions to link the chosen subject to its corresponding image.

Flexibility and Ease of Updates

The approach shown allows for easy updates and additions to the planner. For instance, adding new subjects or changing images is straightforward. The speaker emphasizes the versatility of the INDEX MATCH function over VLOOKUP, as it offers more control and adaptability, particularly when rearranging data.

The video also touches on the specifics of resizing rows for better image visibility, the importance of unique identifiers for dropdown options, and the benefits of expanding dropdown lists for future additions.






How can I use this function, lower, upper, or proper as a conditional formatting? And then the other question is, how can I use this to apply on a bunch of cells without having to double the cells?
How to add a timestamp to done tasks. Learn how to easily add timestamps to your done tasks! Say goodbye to manual formatting and hello to this simple code. Click now to save time!