Stack Exchange Network

Stack Exchange network consists of 175 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.

0
votes
2answers
32 views

Typing in a particular cell and convert it in upper case [closed]

After I typed in a cell a text value (this is in lower case), what do I need to do (after typed it), to become it as the upper case? How do you change the case to upper case for a whole column in ...
1
vote
1answer
19 views

Assigning a value to cell depending of another cell list of item value

Here is the situation: I have column I with the header as "Design" and column J with the header as "DETAIL", then I have column K with a header as "TYPE_W". OK on this last column K, I selected from ...
0
votes
1answer
15 views

Query with Date Selector. Both must be inputted or no information is available?

Currently using: =IFERROR({QUERY(PRI!A4:T,D1,1);QUERY(NON!A5:T,D1,0)},"Nada") This is used to grab information from two spreadsheets with the same number of columns, with PRI grabbing data with ...
1
vote
1answer
17 views

If a dropdown value is selected, how do I add it to another column and then gather the sums?

So I have a dropdown in Col K with "Apple, Oranges, Grapes, Cherries". If a kid wants to order a specific fruit, I want it to auto-populate the section of Col N in green and then sum the total ...
0
votes
0answers
22 views

Auto date insert when opposite cell is populated in different columns - only if in a particular sheet in a document

I found this solution to autoinclude the date in a column when another one is modified and it works great. Auto date insert when opposite cell is populated What Im looking to do is: To modify this ...
0
votes
1answer
15 views

How to increase formula by increment of 10

I have a spreadsheet with one value per year. I would like to take the average of decades (with, obviously, 10 cells per decade), so I set up a formula along the lines of =AVERAGE(A(1+10):(A(10+10), ...
0
votes
1answer
34 views

Arrayformula for running MAX of separate column

I'm trying to use ARRAYFORMULA in Google Sheets to get the max figures from a separate column only up to the row being calculated each time but can't for the life of me figure out how. It sounds more ...
0
votes
3answers
43 views

Google Sheets COUNTIFS Range of cells as criteria possible?

In the Google Sheets Formula =Countifs, is it possible to have a range as a criterion, for example: =COUNTIFS(ExampleSheet!A1:A10, "Some criteria", ExampleSheet!B1:B10, List_of_Names!C1:C) I couldn'...
-1
votes
1answer
34 views

How to test performance of a Google sheet formula?

I was wondering, whether ARRAY_FORMULA() enclosed by COUNT() work much slower than COUNTIF() without ARRAY_FORMULA(). How to test/compare performance of formula that uses a range as parameter?
0
votes
1answer
19 views

Google Sheets automatically converts a string to a number

I am using Sheets as a front end to view tons of different IDs and credentials associated with my authenticated access to a 3rd party API. When calling this function, I get this result: ...
0
votes
1answer
16 views

Combine cells retaining formatting within a sheet

I want to combine columns of texts while preserving the formatting (e.g., underline, italics). How can I do that? I know how to use =C1&CHAR(10)&C2 ... but the formatting is lost.
1
vote
1answer
16 views

Sort by a Column While Maintaining Information in a Row

I had a previous question with lots of support and it solved my problem. The next issue I have is sorting the data while bringing the information in the corresponding row with it. A link to the ...
0
votes
0answers
16 views

How to copy cells (relative) and paste into an absolute cell location in Google Sheets

Trying to create a function where I can copy an entire block of cells depending location of button where script is activated (getting them to select works fine): spreadsheet.getCurrentCell().offset(-...
0
votes
3answers
49 views

How Can I Sort a Data Range in Google Sheets Based on an Data Range?

I have been looking for a way to sort this information. I need to sort these items in this order: BK, BB, BV, BIV, BIII, BII, BR. I was trying to make a Google Script to get it, but I could only ...
1
vote
2answers
19 views

Formula for Calculating Percentile Race Finish in Google Sheets

I've Googled and found many ways to calculate percentile, but most seem to be in regards to an array of numbers as opposed to a single number. I'd like to know in what percentile I finish in a given ...
1
vote
1answer
17 views

Report the date/column header for the first and last non-zero entries for each client/row

I have a sheet with client names (Column A) and the number of visits to the business by mm-yyyy (Columns B - AC). I'm trying to find the first month that they visited the business and the last month ...
0
votes
2answers
32 views

How can I transfer the contents of hundreds of emails to a single Google Docs/text editor document?

A friend and I write together, via email, back and forth a little at a time. we have already once taken a few hundred thousand word story and painstakingly copied and pasted it into a Google document.
0
votes
0answers
10 views

Google Slides with data from Google Sheets

Is there a way to insert Google Sheets data into Google Slides? I want to print certificates using Google Slides (certificate template) and then grab data from Google Sheets, like a mail merge. Is ...
0
votes
1answer
15 views

MATCH a cell value, then return the value of the cell next to it

Is there a way to use a MATCH formula to look up where one value appears and then use that returned index as the point to reference to find another value? Right now I have a spreadsheet set up where ...
0
votes
2answers
18 views

Function to search columns and tally data

I am wondering if I can write a function to search a column in my spreadsheet for a specific name, then return x number of values within the row(s) that contain the name. For example... M4 holds the ...
-1
votes
1answer
16 views

Are Boolean expressions short-circuit evaluated in Google sheet formulas?

Suppose I have the following expression repeated in a long column: =IF(OR($U2=""; COUNT(aHugeRange)=0); ""; SUM(aHugeRange)) I would like to avoid performing COUNT() and SUM() as much as possible to ...
0
votes
2answers
39 views

A formula to get total number of cells in an open-ended range

I need a formula to calculate the total number of cells in the range, including empty and not empty cells (i.e. the "size" of the range). It is more useful for open-ended ranges, like A:A, $3:$3, or ...
1
vote
2answers
18 views

How to combine a formula with text in one cell?

My spreadsheet contains data divided into weeks in a year and I would like to combine the following two pieces of information in one cell: =ISOWEEKNUM(TODAY()) Calendar week 'Calendar week' being ...
1
vote
1answer
15 views

vlookup sum to calculate total hours assigned next to dates

I'm attempting to calculate a total number of hours based on entering 2 different dates. Each day between the dates has assigned 'hours worked'. I'm trying to use Sumproduct or Arrayformula and Sumif,...
0
votes
1answer
28 views

How can I use GOOGLEFINANCE to return a share price in a certain currency?

I have shares in different currencies that I want to track in only a single currency. I currently have a cell that returns the currency and one that returns the price, I then multiply them. However, I'...
0
votes
0answers
12 views

Google Form File Upload - Change File Name to Match Form Value

I have a Google Form for contact information. The form asks for Given Name (col A), Family Name (col B) and a File Upload restricted to images only (col D). I have a formula which displays the Full ...
4
votes
4answers
59 views

Joining two rows of data with alternating delimiters

I'm working with two rows of values that are related to each other and trying to build a formula that will compile the data into one cell, with syntax that uses two delimiters. If a cell in the ...
0
votes
1answer
26 views

Concatenate using an array formula to result in last name.firstname

We have a form that asks for the students first name and last name. I am trying to using concatenate and an array formula to display "Lastname.Firstname". I used: =ARRAYFORMULA(CONCATENATE($B$52:$B,...
1
vote
1answer
48 views

Using Google Script to place Duplicated Google Form Responses in one Column?

I have a Google Form, with multiple sections, and duplicated questions. Google Forms Automatically creates separate columns in the resulting Spreadsheet for each question, even if it is the exact same ...
0
votes
1answer
22 views

Concatenating URL with a value to create link, but links only works partially

I have a column of cells in Google spreadsheet with values such as: b2397 b9504 b3333 b8655 bxxxx I would like to combine each value with a URL, for example: http://websitex.com?id= ...
0
votes
2answers
102 views

Row height would not auto-adjust to fit content anymore

I selected the whole sheet and re-sized the height of all rows at once to a certain height by clicking "Resize rows 1-1000" in the context menu. Then I entered some text in a cell and the text did ...
0
votes
0answers
19 views

Google Docs thinks that I am using an Apple keyboard even though I'm not (non-Chrome browser)

I'm using the Gnome Web browser on Linux which happens to be my favorite browser. I'm having an issue with all Google Docs applications where it assumes I'm using an Apple keyboard when I'm not. This ...
0
votes
2answers
21 views

How to export CSV without commas in unpopulated cells?

I have a form converter which simply rearranges columns. I use a script to paste Values only into another sheet for export and uploading onto another website. However, when I go to export, it seems ...
0
votes
0answers
61 views

How to append data to desired column in Google Sheets?

I have created Google sheet having two columns A & B. I want to enter "In time" in column A and its corresponding Out time in column B. I have created two python scripts which append data to the ...
0
votes
1answer
12 views

Average value using different sheets on Google Sheets

I have a data set that's separated by year (so, the first year is Sheet1, the second year is Sheet2, and so on. There are 18 years in total). I want to average the data from different years. At first, ...
1
vote
1answer
22 views

How can I list rows meeting a criteria and limit the amount of rows by a sum of the list?

I am trying to create a scheduling assistant within Google Sheets. I need to list out tasks currently scheduled for a specific date but there is a volume cut off that needs to happen. Client ...
1
vote
1answer
13 views

Google Sheets Circular Dropdown Dependency

Suppose we have 2 columns Id & Name which contained in sheet1. Now in sheet2 have dropdown for Id & Name which user can select and it Lookup value for each other (select Id from dropdown, Name ...
0
votes
2answers
16 views

How to work over rows from all other sheets in Google Sheets?

I have a 'summary' sheet and 12 other sheets named 'Jan20', 'Feb20', etc. In the summary sheet I have this formula (to calculate Uber costs): =FILTER('Jan20'!F2:F, REGEXMATCH('Jan20'!D2:D, "UBER")) ...
1
vote
1answer
18 views

Join multiple ranges when using conditional formatting

I have a simple conditional formatting custom formula which highlights duplicate values in a column: =countif(A:A,A1)>1 I have another column on another sheet (same workbook) which I would like ...
0
votes
1answer
7 views

How to sum values of the same row from different columns in a separate sheet with QUERY?

I'm trying to add up the value of cells in the same row but different columns to another Google sheet. Example below:
0
votes
1answer
31 views

Add row to Google Sheets and keep formulas from previous row

I want to add a row to my spreadsheet but keep the formulas – and only the formulas – of the previous row. The #1 solution provided here doesn't help me because it also copies non-formula information, ...
1
vote
1answer
27 views

How do I lock a sheet in Google Sheets at a specific time every day?

I would like the sheet protected from 6pm - 10pm every day. I have the following script but it doesn't seem to protect the sheet at the designated time. I have this script with the Time-based trigger, ...
0
votes
1answer
26 views

Google Sheets Custom Number Format - Unchanging #<1000

I am well aware of the excellent Custom Number Formula: [<999950]0.0,"K";[<999950000]0.0,,"M";0.0,,,"B" Which abbreviates #,000 numbers to #K, #,000,000 to #M etc... However, I am looking for ...
0
votes
1answer
43 views

Convert Number and Text to Number

I have a spreadsheet in Google Sheets with data like this: In sheet 2, in column pricelist, the below data is there. I want the summary in sheet 1 to have the same. I have used index and match but it ...
0
votes
0answers
22 views

Using custom function in QUERY in Google Sheets results in “unknown function:”

I have a custom function written in Google Script for Sheets named sheetnames(). It returns the names of individual sheets inside a worksheet. When used in a regular cell-like =sheetnames(), it ...
0
votes
0answers
12 views

How does one import an RSS url as a RSS formated URL?

Currenty I am currently trying to aggregate a few of these urls in order to pull html which I want to display in my Sheet. Problem But currently I am just importing RSS from a news aggregator using ...
0
votes
0answers
14 views

Calculate a running average of the last 10 cells that include data (removing the two highest results from the last 10)

I would like to calculate the running average of the last 8 entries in each row of the spreadsheet shown in the image below (the further right the cell the more recent the data). Some cells will be ...
0
votes
1answer
22 views

I have a google Forms that needs to have duplicates removed and statistics updated. Can this be done?

I have a Google form that was used for award votes and nominations. Specifically, each question contained drop-down value and the voters each selected an option from the list. At the end of the form,...
0
votes
1answer
31 views

How to get a Sample Standard Deviation values that match condition?

I am trying to get the standard deviation of a subset of data. I have two sheets Name Value a 1 a 2 a 1 b 5 b 5 In a separate sheet I have: Name StdDev a b GOAL: I am trying ...
0
votes
0answers
23 views

Auto timestamp based on specific value

Currently, I am using below code to update timestamp in the next column. But I want to modify the code to update timestamp only when the cell value is True not on a change of cell value. function ...