Questions tagged [google-sheets]

For questions about the Google's web application to create and edit spreadsheets on a web browser. Don't use it for the Google Drive Excel files editor. Please consider adding a demo spreadsheet to your question, this will greatly increase the chances to get a satisfactory answer.

Filter by
Sorted by
Tagged with
0
votes
0answers
5 views

Google Sheets - Preventing certain rows from being sorted

I'm trying to make a list of all my games and where they're from to try to kill somewhat of a backlog. The main problem I'm having is the sorting function since I don't know how to freeze multiple ...
0
votes
0answers
6 views

Justify a cell's text in Google Sheets

How do you justify text in Google Sheets? It's not available in the Horizontal align toolbar item or from Format > Align. This answer to an unrelated question mentions "justify text mode" but doesn'...
0
votes
0answers
13 views

Synchronize multiple cells on Google Sheets

Building off of this question: Sync multiple cells so that if any one of them is changed, the others change as well Is there a way to synchronize multiple cells without individually entering them (e....
0
votes
0answers
6 views

CTRL F Search Hot Key not working

Ctrl F stopped working in Google Sheets. I believe someone disabled it. How do I re-enable this feature?
0
votes
0answers
15 views

Google Sheets To-Do List: INDEX/MATCH and Cell Updating Questions

I am currently working on a to-do list dashboard for my household chores (https://docs.google.com/spreadsheets/d/19PhPMUc121KY8ndZq_4HURJ-9Re4K6CNtZgdgAIfzic/edit?usp=sharing) and have a few questions....
0
votes
1answer
13 views

How do I make the color of a cell change if the value has been modified in Google Sheet?

Basically, if the person enters "12" in his/her column, how can I make the cell change to a different color when the same person changes the value again? This would let other people know if there ...
0
votes
1answer
20 views

Pretty print google sheet formula?

Finding answers on Web Apps SE or Reddit is wonderful, but if you don't understand them you can't modify them. I recently came across the following as part of an answer: ={"Corrected List 2";...
0
votes
0answers
13 views

Why doesn't the GOOGLEFINANCE function work inside a calculated field in a pivot table?

I can use VLOOKUP to find something from a calculated field, but the GOOGLEFINANCE function just gets stuck loading forever: In this example, GOOGLEFINANCE is used to find the stock name, the "Tag" ...
0
votes
0answers
11 views

Is there a way I can filter a list of email addresses that contain words from another list in Google Sheets?

I have a list of thousands of email addresses and I'm trying to filter out ones that contain specific words. I have the list of words in the same spreadsheet and currently I am filtering the email ...
0
votes
0answers
16 views

Using same Google Spreadsheet with multiple people but different instance

I currently have 10 different Google Sheets that I manage. Multiple people use each sheet daily and I normally have to create new copies each time they need to use them. Is it possible to allow the ...
0
votes
0answers
17 views

More compact version of nested substitute function possible?

I want to make about a hundred changes in a column on a spreadsheet. These changes have to be remade each time a new copy of the original comes. The changes are spread over several thousand rows. ...
0
votes
0answers
6 views

Monthly Client retention/attrition calculation

I have a spreadsheet exported from our gym management software into google sheets and I am trying to figure out how I can can calculate our retention rate/attrition rate. As we allow month to month ...
0
votes
0answers
14 views

Dynamically retrieve entire row in Google Sheets for computation

Objective:I want to retrieve the content of the last row of a Google Form and do a simple computation with that and later return that value to the user. Example: The form contains two questions: "How ...
0
votes
1answer
23 views

How do I convert a number on one column to another number on the next column in Google Sheets?

Here is the range of hours I am trying to convert to days. 0-2 days equal 5 hours 3-5 days equal 10 hours 6-10 days equal 15 hours 11-15 days equals 20 hours In the picture, the highlighted column (...
0
votes
1answer
9 views

Google Sheets - conditionally format a cell based on whether the content of the cell appears in a column from a different sheet?

I have a sheet called Master that contains a column of names in cells A1-A60 (e.g. A1: Ann,A2: Bob,A3: Chris, A4: Donna, etc.). I have another sheet called Subsheet that contains names that may or ...
0
votes
0answers
9 views

Select certian coluumns from Table A when a certian cell combination is not present in Table B

I am currently using the QUERY function in Google Spreadseets to select columns from different table. I have two tables (TableA and TableB). I want to SELECT * when the combination of Col1 and Col2 ...
0
votes
0answers
11 views

How can I pick an item given it's range, from a number within that range

https://docs.google.com/spreadsheets/d/1GoaRd0xJZLPPaXJhmcfEQgW_GNsQyyy_Xa51G-nixqc/edit?usp=sharing Please view the spreadsheet. Essentially I have a list of albums, with a rating. What I want to do ...
0
votes
0answers
14 views

ImportXML error: 'imported content is empty' when selecting for text within a list item

I want to scrape a text within this website in google sheets with the IMPORTXML function. Through dev tools by inspecting the text in question, I get the corresponding HTML: <li _ngcontent-udl-c4="...
0
votes
0answers
19 views

Conditional Formatting on Google Sheets using regex

I am now going in circles, I tried to look for answers here but I am still stuck. I want to highlight a cell using conditional formatting if the value of that cell is present anywhere in a specific ...
0
votes
1answer
11 views

How to copy range from one sheet to another column with offset

I have a range of data on sheet5 which needs to be copied and inserted 27 columns in from the end of sheet1. I can get it to insert the column in the right place but it doesn't copy the data. I am new ...
0
votes
0answers
17 views

If cell contains certain text then another cell calculates a number

I have a budget spreadsheet with two sheets. First sheet is titled "Summary" and the second "Transactions," where I log financial transactions. I want a cell (Q18) from my first sheet to return the ...
0
votes
0answers
12 views

Google Sheets: auto-fill with increment to filled cells

So basically, what I'm trying to do in Google Sheets, is: I have a column called 'Name' and a column called 'Badge number'. Badge number consists of 2 parts: category and increment number. For ...
0
votes
0answers
9 views

stock optimization using covariance matrix using Markowitz theory

I'm trying to use a template to see what it says about my stock portfolio. It uses a covariance matrix with the data from excess returns. The template instructions say to use an add-on to calculate ...
0
votes
1answer
18 views

Sheets - Determine Cell Number with Equation

I'm trying to reference a cell by the following equation: =A(51-(48-(COUNTIF(Info!A5:A52,"*")))) A51 is the last possible cell that information could be in (hence the 51). 48 is the total number of ...
0
votes
0answers
10 views

How to setup Google Sheet to import data to a new row when requirements are met?

So I have a Google Sheet I've made at work to simplify our needs for receiving product, the old sheet was a template and people handwrote everything which leads to confusion for people putting the ...
0
votes
0answers
16 views

If then with a checkbox

I have a Google Sheet. I am trying to do an if then statment. If cell C8=True then I want it to perform a function. I know this is not right but wanted to give you guys example of what I am writing ...
1
vote
1answer
31 views

How to query on a Google Sheets to sort by month

I'm using this Sheetgo Attendance template on Google Sheets, but it's designed for US Schools so it's needed to sort by January to December. I will provide below the assets that I've have about it. ...
0
votes
0answers
3 views

Sorting by week in google sheets Analytics add on

I just want to run a simple weekly traffic report with the Google Analytics Sheets add on. It does work fine, but I can't seem to figure out how to sort the weeks in chronological order with the jump ...
0
votes
1answer
14 views

Fix my script? How can I automatically create events in google calendar using dates from google sheets?

I read that it is possible to link a google sheet to google calendar, so dates can be used to create events in the calendar. I used script I found around about, but absolutely nothing happens. Can ...
0
votes
0answers
20 views

Print First Modified TimeStamp of edited Cell in a Column range [duplicate]

This code by @Ruben prints out the timestamp for the first modification for A1 into B1. How to do the same but for the whole column so A2:A printing results into B2:B? // Global variables var origin =...
0
votes
0answers
12 views

My function links all required cells between google sheets except one!

I have this query =query(IMPORTRANGE("<https://docs.google.com/spreadsheets/d/1uxIkcjOTxFAd25Ukzji3VJKoFbAKybuFXfHlBzaGDWc/edit#gid=1385200762>","Master!A3:y"),"select Col2, Col3, Col12, Col13"...
0
votes
1answer
12 views

Variables in Google getRange

I have a simple need to copy a range (E4:G4) and paste it X rows below, determined by the value of a cell, C2. I think I may be misunderstanding the offset function since when I run the routine, I get ...
0
votes
0answers
15 views

Output word within a string of words, if it matches to a value within a named range

I have a list of search queries for cars like: hyundai ioniq hybrid rav4 hybrid hyundai ioniq electric toyota yaris petrol vauxhall automatic car I also have a list of models of the cars (ioniq, ...
1
vote
1answer
14 views

Sum depending on value of Column B and Date in Column C

Column A: Name Column B: Date (mm/dd/yy) Column C: Value I want to sum column C every time column A is a specific name & the corresponding date is before 2020 Small sample/Example of what I'm ...
0
votes
1answer
7 views

How to convert datetime string to datetime value in Google Sheets?

Let's say cells A1 and A2 have these values: 13 Jan, 02:21 am 12 Jan, 10:15 pm And many more values like those below in that column. What formula could I use so that the B column would look ...
0
votes
0answers
10 views

Need some help inserting break to prevent “Exceeded maximum execution time” Google Sheets script

I'm using a Google Sheet to populate drop downs in a Google Form. There's not a lot of info to be transferred as such, but it's repeated 76 times... I'll paint the picture first, so you have an idea ...
0
votes
1answer
16 views

Google Sheets Formula - Trying to Sum, based on multiple cell criteria

I am looking for help with Google Sheets formula. The formula I need is - If A:A=LG and F:F=Indexed then sum the total of P:P for all of those instances. If A:A=PG and F:F=Indexed then sum the ...
0
votes
0answers
7 views

Can you lock formatting rules? [duplicate]

I have set up a sheet as a rota. It uses a few conditional formatting rules to highlight things that might need attention, like if a cell is empty and if a date is within the next 2 weeks. However as ...
1
vote
1answer
19 views

How to see all custom formatting rules in a sheet, not just those applying to current selected cells?

As far as I can tell, in Google Sheets you only see a list of formatting rules applying to your current selection. Is there a way to see a list of all rules that exist in a sheet? When people copy-...
0
votes
1answer
15 views

Is accessing multiple Google Sheets Files(not multiple google sheets within the same Google Sheet File) in one script possible?

I was wondering if I can copy a certain range of cells in Google Sheet File A and paste it to another Google Sheet File B. More specifically, I'm referring not to sheets in the same Google Sheet(eg: ...
1
vote
1answer
22 views

How do I create a pie chart in google sheets without a category or two from my data?

I have a list of data, showing 4 items. Cats, dogs, giraffes, eggplant. I want to create a pie chart in google sheets that shows the pie with just cats, dogs, and giraffes. I don't want to show the ...
0
votes
0answers
7 views

Google Sheets: Histogram of time data (axis numbering)

I've tried making a histogram of my data, which looks like so: 12:00:02 PM 3:00:00 PM 12:30:00 PM 12:30:00 PM 2:00:00 PM 3:00:00 AM … I highlighted the data and selected insert > chart > ...
0
votes
1answer
14 views

Google sheets - tying a numerical value to different texts

I am incredibly new to google sheets and have a basic question, that I can't seem to find an answer for on the internet. Is it possible to count up multiple different texts into one cell, with ...
0
votes
1answer
17 views

Making a chart in Google Sheets

First time poster and currently getting familiar with Google Spreadsheet with using some books and information on the net. However, I couldn't put into words what I was trying to do so I found this ...
0
votes
1answer
14 views

Mass-remove empty sheets in Google Spreadsheets

I have created an interactive search spreadsheet that filters columns based on the user input and the data from another sheet. Since the user needs to be able to enter values in the search cell, I had ...
0
votes
0answers
19 views

INDIRECT cell reference - Can't copy cell as reference cell pattern remains same

I have an Overview Sheet where I want to display a sum of values from another sheet. I'm using the indirect function to grab the sheet name from a cell in the overview sheet. The formula looks like ...
0
votes
1answer
37 views

applying a formula to the data from google forms

I have a google spreadsheet which is connected to a google forms I wanted one formula to be automatically copied to the new lines(Column R) when google form response is submitted the spread sheet: ...
0
votes
0answers
26 views

Google Sheets - Multiple outcomes using If And statements?

I'm working on categorizing tires and rims that I own. I want the sheet to look into the "Width", "Ratio" and "Rim Size" columns to provide me with data to determine which car it would fit on. For ...
1
vote
1answer
83 views

Google Sheets calculate average on variable number of non-adjacent columns?

A Google Sheet is being used to insert markings for students tests. When a single test is done by students, teacher assigns multiple grades for it. For instance, one marking for writing, one for ...
0
votes
1answer
14 views

How to extract digits only from formatted phone number string?

I'm getting user submitted phone numbers in a variety of forms, for example (fictional): (423)700-4000 9002570000 703-203-3000 I want to extract just the numbers so that I can upload them to an app ...