Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

1
vote
1answer
25 views

JOIN two columns in one cell

What should I do if I want every time I fill out column C, to all data from B6 to C14 would appear on the notes?
0
votes
0answers
5 views

Hot to switch master data in google sheet

I have a spreadsheet with an overview of calculating some income based taxes now all tax percentages are fixed and for a specific year I want to move to have a list of master data: the percentages per ...
2
votes
1answer
188 views

Aggregate results by week and month for charts

Is there a way to convert daily values into weekly averages for charting? Sample source sheet --------------------------------------------------- | Data 1 | 1/1/2018 | 1/2/2018 | 1/3/2018 | etc......
0
votes
1answer
20 views

Move ROW upon selecting a specific option from drop down menu

I am looking to use Google Sheets to keep track of my jobs. I have a spreadsheet set up so the status of a job is indicated by a dropdown menu. When a job is completed I would select "Closed" from the ...
5
votes
2answers
7k views

How to hide unused columns in Google Sheets

I would like to make a Google Spreadsheet that has a limited column numbers but I cannot find where is this configured. Looked for this setting with all that I could think would make a good keyword : "...
1
vote
0answers
14 views

Enable grouping and ungrouping of rows in a locked worksheet (Google Sheets)

I need to share a spreadsheet with some locked cells, but it is necessary that the user can use the grouping and ungrouping functions of rows. Within the pooled range there are locked and unlocked ...
0
votes
2answers
29 views

Google scripts: Contacts to Spreadsheet Issue. Names with Address, Phone Number, Email, and Job Titles

I have over 500 contacts in Google. I am wanting to list all the contacts that are associated with a particular company into a sheet. When I run the following script, I receive a timeout error. ...
1
vote
1answer
160 views

Change text case so that first letter of the sentence is capitalized

Want to automatically change text case as I type on Google Sheets so that first letter of the sentence is capitalized.
1
vote
1answer
177 views

Range notation and current row

I wonder how does work the following and is it documented somewhere? I have the sheet: A B ----------------- 1 2 3 =$A:$A 4 5 =$A:$A For column B (row 3 and 5) I see ...
0
votes
1answer
26 views

Is it possible to use checkboxes to determine what data is in a list?

This is hobby related, so feel free to ignore. I want to learn how to use Google sheets, and am doing so by building a partial DnD character sheet, piece by piece. I am trying not to use scripts as ...
1
vote
1answer
22 views

How to multiply a query column by a cell refference on Google Sheets?

Below is the query that I use, and it works: =QUERY(Input!A2:I33; "SELECT '000', A, B, C, D, E, ' ', F, ' ', ' ', ' ', G, ' ', ' ', ' ', '"&TEXT(Input!L8;"dd/mm/yyyy")&"'...
2
votes
1answer
2k views

Can I precisely position charts in Google Sheets?

I am using Google Sheets to create a "Monthly Report" spreadsheet for a product, and am attempting to make an attractive, compelling experience for the consumers of the report. In this spreadsheet, I ...
2
votes
1answer
2k views

Snap chart to grid or place a chart in a cell?

Is there a way to snap charts to the grid? In my spreadsheet, my chart hangs a little below my table, but I would like the chart to expand/contract with the cells on the left. If this is an easy ...
-1
votes
2answers
429 views

Sync two cells value

Is there a way to way to link two cells so when one is altered the other is altered also? Like this: I alter `A1` to 1, `B1` is now 1 I alter `B1` to -1, `A1` is now -1
1
vote
1answer
18 views

How to join VLOOKUP results of multiple sheets into one cell

I am trying to use TEXTJOIN across multiple sheets, IF a cell is is the same I would like it to return the value from another cell in all sheets that are associated with it. In this example, I am ...
0
votes
2answers
16 views

COUNTIF to display which results meet criteria

I'm working on a spreadsheet, where I'm using COUNTIF to check the number of times a sheet range meets a criterion. This works. I'd like to see which cells (their contents/locations) which met the ...
0
votes
2answers
44 views

How do I find the address of last non-blank cell in a row (while skipping columns)

Basically, I need to do what the title says on my spreadsheet here. So for row 3, I need to find the address of the last non-blank cell in that row, while skipping the grayed out columns (with the ...
0
votes
2answers
12 views

How to scan rows for match with ArrayFormula?

Trying to search a row for a given value, but want to make an ARRAYFORMULA so that every time a new row is added the search will be performed on that row. Currently using =IF(COUNTIF($A2:$EI2,"="&...
1
vote
2answers
18 views

Conditional formatting rule that compares against a range of values

I have a list of 2019 dates (col A) and holiday dates (col G). Wherever there's a date in col A that matches any date in the range G2:G20, I would like to highlight it in grey. See this sample ...
0
votes
2answers
9 views

FILTER for multiple conditions including OR

How can I use FILTER in a single formula to check for multiple conditions, such as in this case? The filtered range must be equals1 AND notequal2 AND equals3 OR equals4 This is of course an ...
1
vote
1answer
15 views

How to return unique list of text pivot by Query

I'm trying to mimic a pivot table behaviour where you would grab all distinct values from Col B and, by each of those unique Col B values, you associate all each respective values, that must also be ...
0
votes
2answers
16 views

How to count cells in a row while skipping specific columns?

Basically, I need to count non-blank cells in a row and I need to skip certain columns/cells in that row. I'm sure this is simple combination of functions but I can't seem to find a solution to this.
0
votes
1answer
9 views

Spell check with formula or apps script

I have text in the table. I want to check the text whether there is a typo or not. Usually to check my typo using the default Spelling Tool from Google Sheet. The question is: Can the spelling check ...
1
vote
2answers
16 views

Formula for PROPER Case with Apostrophe

I'm using Google Sheets glued together (not be me) from multiple sources where some of the names are in all caps. In trying to clean web-ready content, I have good success with the PROPER() function ...
2
votes
1answer
74 views

How to automatically create a templated Google sheet?

I'm trying to figure out how to automatically create a templated Google sheets worksheet when a new entry is made in a different sheet. So the flow would be: Person's name is added into Google sheet ...
1
vote
2answers
25 views

Next Payment Date

I am wondering how to calculate the next payment date a client has. We know when their payment date starts, what the frequency of their payments is, and of course today's date. However, I cannot seem ...
0
votes
2answers
15 views

How to quickly re-arrange sheet order in Google Sheets?

If I have a Google Sheets document (Workbook in Excel terms) with 20 sheets (Worksheet in Excel terms) and I want to rearrange the order of the sheets currently the only way I know to do this is to ...
0
votes
0answers
9 views

Updating a total and calculating from the new total

I have a Google sheet that I'm sharing with my students that is being used for a stock market game we are playing. There is an opening price for the shares and a column for each group. I want the ...
0
votes
2answers
32 views

How to group data (by row and column) in a Google spreadsheet?

I have data as follows: Name | Value | Y/N | Value | Y/N A | 10 | Y | 12 | N B | 100 | Y | 120 | N A | 20 | Y | 10 | Y B | 200 | N | 120 | Y C | 1000 |...
0
votes
1answer
9 views

How to apply conditional data validation when a cell with a defined value is changed?

I have a spreadsheet that's basically a grid checklist, let's say 10 columns and 20 rows. There are multiple cells that have N/A as the value, and I'm worried going through the checklist that one ...
37
votes
13answers
97k views

Shortcut to insert new row in Google Spreadsheets?

Is there a way to do this without using the menu? I have seen references to ctrl + space and ctrl + +, but this no longer works (zooms in and out in Google Chrome)
0
votes
0answers
6 views

How do I send changes done in a Google Spreadsheet document turn into a HighRise notification? [on hold]

Is there a way to set this up? I BCC whenever I contact customers and the replies show up in HighRise through the dropbox email. But can I make changes done, or a daily summary of changes made to a ...
-1
votes
0answers
21 views

Replace formulas with results in google sheet only if the result contains value in it, else i want formula to stay intact

I am working on a google sheet where i am using a vlookup formula. I want to replace formulas with results, only if the result contains value in it, else formula should stay intact. The below code is ...
0
votes
2answers
49 views

IMPORTXML error - empty table

I am trying to import a table from the lower part of the following website to Google Sheets: https://vistacapital.com.br/ I used the formula: =IMPORTXML("https://vistacapital.com.br";"//*[@id='sos-...
0
votes
1answer
417 views

Exporting Google SpreadSheet to PDF (filename referring to a cell D7)

I have developed a script which allows me to export a spreadsheet to a pdf file with a click of a button. I have assigned the script to a macro so that it is triggered by clicking on an image. Script ...
0
votes
1answer
14 views

Alternate to nested if statements (alternate posing of question)

Sample Data Sheet: https://docs.google.com/spreadsheets/d/1EIQcpzwdaoNSKEPu-F1NCUOtEjS6Ez0P73dmotClT7Q/edit?usp=sharing The end goal is to have a Month + Year show up in a single cell, based on the ...
0
votes
1answer
19 views

Google spreadsheet mail as Excel attachment

I want to mail Google Spreadsheet as excel attachment, and also I want to write 4 to 5 lines in the body of a mail, and also I want to send this mail to many peoples some in "To", and some in "Cc" can ...
1
vote
1answer
12 views

Query Formula excludes results with new line character

It seems there may be a bug in Google Sheets' Query formula where results with a new line character (char 10) are excluded when the column is filtered. Is there any way around this or am I doing ...
1
vote
2answers
121 views

Using multiple AND conditions in one IFS fromula

I’m trying to make sheets calculate my delivery times based on which locations the items are shipped between. Tried combining the Ifs and AND formulas, but I keep getting errors. (Let’s say the ...
0
votes
1answer
33 views

Alternate to Nested IF statements

Goal here is to have the sheets search a row, then once it finds the first instance of >=0, return the cell value that contains the month+concatenate the value in the cell next to it (or any other ...
0
votes
1answer
19 views

How to apply scripting only to rows with data in them?

I have a Google Sheet that adds multiples rows per day as forms get entered. What's the best, most hands-off way to apply formulae to rows as they get added? I've built a script, but it applies the ...
1
vote
1answer
37 views

Dynamic protection based on cell value

In my Google spreadsheet, I want to protect all cells below a checkbox if it's value is true and unprotect if it's value is false. Every cell in row 3 is a checkbox that this has to work for. Example ...
4
votes
2answers
6k views

Using query to find the entries in one column which are not in the other

I am using Google Sheets to track changes in a group's roster. Cells B2:B240 contain the old list of names, and C2:C240 contains the new list. Both lists are alphabetized. There is a lot of overlap ...
1
vote
1answer
10 views

Restrict entry to a group of cells based on the value in another cell

I am creating a work schedule where the staff enters their own schedules. I only want a certain number of staff each shift. I already use COUNTIF to show the number of staff signed up for each shift. ...
0
votes
1answer
14 views

If checked, add row to data chart?

Basically, I have been working on some automated solutions to show the date and data for the most recent 4 weeks on this data sheet. There are still some bugs to work out, but the last thing I need to ...
2
votes
1answer
25 views

Timestamping purchase entries

I’ve created myself a budget tracker where I can track what I bought and how much I spent across a few categories. I specified column B (purchase entries are rows) to be the date and time at which I ...
0
votes
4answers
81 views

multiple IF statements with between number ranges alternative

How to efectively deal with multiple IF-statements and with multiple "between number" ranges in Google Sheets' spreadsheet (e.g. C column based on B column) with a rule set as follows: 01-10 11-20 ...
3
votes
1answer
712 views

How do I interleave two lists or data in Google Spreadsheets?

I want to combine two lists in Google Spreadsheet so that the cells interleave. Both lists have the same amount of elements. So that: 1 A 2 B 3 C becomes: 1 A 2 B 3 C ...
0
votes
0answers
11 views

How to record Google Form responses back to Google Sheets

I am a beginner in Google scripts. I have learned how to write to a Google scripts sheet using the examples below. However, I do not know how to set the range ("?") Because the range ("AB7") will ...
1
vote
2answers
15 views

Imported values in Google Sheets from Google Forms

Using Google Forms for a quiz... The quiz is graded /30. Value is transmitted into Google Sheets. Cell C2 displays a value of grade... here, 14/30 (as an example) Value for cell C2 reads as 14 ...