Last call to make your voice heard! Our 2022 Developer Survey closes in less than a week. Take survey.

Questions tagged [google-sheets]

For questions about Google's web application to create and edit spreadsheets on a web browser or app. 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 chance that you get a satisfactory answer.

Filter by
Sorted by
Tagged with
0 votes
1 answer
27 views

Google Sheets - Merging 4 Columns into 1, ignoring blank data

I have a problem which I'm hoping is a pretty straightforward one. I'm sure it's from high school computing or something, but I can't quite define the theory very well (possibly data redundancy/...
user avatar
0 votes
0 answers
22 views

Resize cell name field width in Google Spreadsheets

Cell name field width is too small. If I want to name a field with a longer name, which I usually do want, in order to make names descriptive, there is no enough space. Is there a way to make it wider?...
user avatar
  • 123
0 votes
1 answer
21 views

How do I make text in a column appear after the rows have been filled?

I have made this spreadsheet to help our mechanical workshop track labour. I have a problem on my spreadsheet where column K displays "follow up". The end users (technicians and foreman) ...
user avatar
  • 3
0 votes
0 answers
18 views

Calculating total number of VISIBLE checked (=TRUE) boxes in a range

I have a range of cells containing checkboxes that are manually ticked based on criteria met and applied to a total value calculation (TVC). The current formula =countifs(C16:16,TRUE) worked for what ...
user avatar
  • 1
0 votes
1 answer
26 views

Using SUM, ARRAYFORMULA and VLOOKUP in Google Sheets

I'm trying to SUM the hours of employees. The list is sorted by WE date and name. The employees name is in U3. I tried multiple variations of: =ARRAYFORMULA(SUM(VLOOKUP(U3,Data_for_2022!B2:K2000,{8,9,...
user avatar
1 vote
1 answer
44 views

How can I render a Google Sheets Cell's value in a Google Doc?

Currently, it is possible to click a cell in Google Sheets and "get a link", however, when this "link" to the cell is inserted in a Google Doc, it hyperlinks to the Google Docs app,...
user avatar
0 votes
1 answer
25 views

In Google Sheets, how would you use the query formula to create two columns populated with data from a third column based on criteria of a fourth?

To all: This is my first foray into using a forum for my specific question, so, please, let me know what additional information might be necessary. I am providing some information below; yet, if it is ...
user avatar
0 votes
1 answer
17 views

Pull some data, based on a date

I need to pull some data, based on a date. QUESTION; what is the value in column D, based on a date for example 'TODAY' or 2/4/2022 I ideally want to populate this detail onto another tab, where it ...
user avatar
0 votes
1 answer
25 views

In Google Sheets, how do I make an ARRAYFORMULA be the Boolean combination of two other columns?

I.e., if I have: A B TRUE TRUE TRUE FALSE FALSE TRUE FALSE FALSE say I want to create a column C that is A OR B: A B C TRUE TRUE TRUE TRUE FALSE TRUE FALSE TRUE TRUE FALSE FALSE FALSE Is ...
user avatar
0 votes
1 answer
17 views

How do I highlight cells in one range when their values are contained in another?

I have range A:A which contains unique, sorted values I'd like to check against values in B:Y. How do I highlight cells in B:Y that contain an element of A:A? I'm vaguely familiar with sheets' ...
user avatar
0 votes
1 answer
19 views

Select value of cell to the left of max value in Google sheets

I am trying to select the value of the cell to the left of max value of a column in Google sheets. Please see attached sample code and expected result: https://docs.google.com/spreadsheets/d/...
user avatar
0 votes
1 answer
77 views

Concatenate multiple cells in one cell based on ID

I'm trying to figure out a formula for concatenating data in cells based on a condition. Specifically, I need to output rows for each unique "Conversation ID" (found in column C) and ...
user avatar
0 votes
1 answer
14 views

I Need Help with Formula

Cell A1 displays the current date, A2 is a date 6 months in the future. I need a formula for A3 to display A2 when A1 is 3 months away. I'm new to making any kind of formulas, I have copied and pasted ...
user avatar
  • 1
-1 votes
1 answer
17 views

Average formula in google sheets

im new to data analytics and have a table in fornt of me with unitstocks. The numbers are seperated with a period after to mark, if its an 18.00 or a 120.00 for e.g when i use the formula average on ...
user avatar
-2 votes
1 answer
19 views

Conditioned formatting of a cell in relation to another cell

Cell B1 should be colored red if cell A1 contains a certain string. I thing this should be possible but I don't succed. Thank you in advance for your answer!
user avatar
0 votes
0 answers
15 views

Time chart with time periods during the day [duplicate]

How I can create a chart with time periods for each day (with start and end times of each period)? My main goal - have a possibility to keep several time periods for same day
user avatar
  • 1
0 votes
1 answer
19 views

ISNUMBER() - Shows for cell formatted as number FALSE

Issue: Cell C1 fetches a number from a web page via IMPORTXML. Cell B1 refers to it (=C1). B1 and C1 are formatted as number and also display numbers. However, ISNUMBER for both cells is FALSE and ...
user avatar
0 votes
1 answer
21 views

Creating email address from scratch

I have a set of data which includes first name, last name and company URL. I have created a drop down list to choose an email format from a list of 14 variations eg firstname@url.com or firstname....
user avatar
  • 1
0 votes
2 answers
50 views

Converting Google Sheet to JSON format while rows are vertical

I am trying to convert the google sheets into JSON format using Google App Scirpt. It worked but I am not getting the right format and I am little struck. My sheet looks something like this in the ...
user avatar
1 vote
1 answer
33 views

sumif not working sometimes

I have read similar questions but unfortunately didn't help me. Here is a copy (with sensitive info redacted) of my data. Google Sheet In column U and V I am trying to sum columns D to O if T is "...
user avatar
  • 13
0 votes
1 answer
18 views

Create variable function arguments in Google Sheets

I know that my question illustrates a fundamental lack of understanding, but I have hacked together a sheet that almost works except... I am importing data from googlefinance then operating on the ...
user avatar
4 votes
1 answer
130 views

Rearrange Name Format in Google Sheets using one formula only

Is there a simple way to do this using only one arrayed formula? Here are the names listed in the above image: NAME dela Cruz, Antonio Karl Jr. Woods, Oliver Hasegawa, Ken Sr. Sta. Maria, ...
user avatar
  • 45
3 votes
1 answer
104 views

Regex for JSON parsing in Google Spreadsheet Cell

I wanted to use the formula from this topic to extract "count" value in this JSON from a Google Sheet cell. [{"year":2022,"month":2,"count":720},{"year&...
user avatar
0 votes
0 answers
17 views

Running a script on select tabs in Google Sheets [duplicate]

I am not a coder, but I can normally do enough trial and error to get my script to function in the way I want my google sheet to work. The goal of my script: I want cell A to time stamp anytime that ...
user avatar
0 votes
0 answers
19 views

Locking google sheet after 30 cells have been filled in for schedule

Does anyone know how to limit the number of cells being filled in google sheets? I'm need to fill a schedule, but only 30 blocks can be filled in before I want the screen uneditable. Can you help?
user avatar
0 votes
0 answers
12 views

Can I make the reference cell variable dependent on a formula specifically for the row? [duplicate]

I want a cell to populate with a reference dependent on the value of another cell. For example, I want to specify column D, but I want the row reference to be the value given in cell B3. Can I do that?...
user avatar
  • 1
0 votes
2 answers
37 views

How do you copy a group of cells over while changing their contents?

Okay, so the title might be misleading but it's the best I could come up with. I'm currently in the process of making a bot to solve Mastermind in Google Sheets because I thought it would be an ...
user avatar
0 votes
3 answers
41 views

Need Help! Google Sheets Formula

Is there a simple formula for this? Where it will look for a Name with "- Leader" in Column A and its corresponding Value in Column B, and it will return the Name of the Leader with the same ...
user avatar
  • 45
0 votes
1 answer
43 views

Problem in adding formula in script for updating daily

Newbie to sheets and scripts! I have seen this question,and am partially successful to the extent of creating a new row with date being updated using this script var ss = SpreadsheetApp.openById("...
user avatar
  • 199
0 votes
1 answer
55 views

How to create static/immutable autoincrement column

I am aware of the manual way (add 1,2 in the first 2 rows, mark them, drag the square on the mark-outline down to where u need the numbers) and a number of ways to create the numbers with formula's, ...
user avatar
  • 1
-1 votes
1 answer
67 views

Google Sheets - Pivot Table: how to use sum(values) in filter custom formula

In our attendance spreadsheet present is 1 and absent is 0. In a pivot table we show the attendance per person: sum(attendance). We only want to show people who attended sessions, so we want to filter ...
user avatar
  • 1
0 votes
0 answers
36 views

Average value by highest sums

I'm having trouble with writing the logic for the formula The results should be as above. Markdown table Closing TEU Average per day 23/3/2022 62 no 120 24/3/2022 49 no 120 25/3/2022 168 48 120 ...
user avatar
0 votes
1 answer
26 views

Conditional Formatting in Sequential Order in Sheets

I hope this all comes out clearly, I am in need of some help to save some major time. This is for sports betting tracking systems. I am trying to create a conditional format to where when I enter a ...
user avatar
0 votes
0 answers
24 views

Google Sheet find all Mondays and average their cell value

I have a google sheet that contains each day for a year of sales by day and hour. I need to be able to search all the data, find all the Mondays and return the average.
user avatar
1 vote
1 answer
27 views

highlighting a single cell when partial text shows up in a range of other cells

I work for a physician office and we keep track of which patients are ready in a room and which doctor goes in to see that patient. We use a google spreadsheet to track this information. I want to ...
user avatar
  • 13
0 votes
1 answer
21 views

Google Sheets: How To Reformat Phone Numbers Within One Column?

I have a google sheet in which people's phone numbers are stored. I want them to be displayed in format NNN-NNN-NNNN, and to do this I like to store them in format NNNNNNNNNN set to display that way (...
user avatar
  • 101
0 votes
1 answer
28 views

How to use a range function with VLOOKUP google sheets

I created this function to get the range from sheets dynamically function GetSheetRange(j) { return SpreadsheetApp.getActiveSpreadsheet().getSheets()[j].getRange(3,1,1000,7).getValues() } I wanted to ...
user avatar
0 votes
1 answer
36 views

How to transpose a column in google sheets with varying 'n' values, based on specific text being present in a cell

I have a Google Sheet list of contact details. Unfortunately, they are all in a single Column. Moreover, each set has a varying number of Rows. I'd like to Transpose the Column but don't know how! He ...
user avatar
  • 7
0 votes
0 answers
23 views

Using cut/paste within a filter view causes data loss

The scenario: I'm editing a rather large sheet. I realize that all the entries with "Alice" in their title should go under the "Caribou" column rather than the "other" ...
user avatar
0 votes
2 answers
30 views

Using IMPORTDATA with a csv file with different numeric format than locale

I'm using IMPORTDATA to get some Crypto Rates. My Spreadsheet has Locale settings to Spain and the data from the csv have the numeric format of United States, so I'm getting wrong numbers. There's a ...
user avatar
0 votes
0 answers
7 views

Date of today when an item is sold [duplicate]

Got a question regarding dates. At the moment i have 2 columns: Order date & sale date. I just fill in order date whenever I order something and then the sale date is: =IF(N66="";;TODAY()...
user avatar
0 votes
2 answers
27 views

in Google Sheets, how do I output data and it's sum in one sheet, taken from only certain filtered/duplicate/conditional data from a different sheet?

What is the formula that I need to enter in 'Sheet2' to Output this table from the data in 'Sheet1'? I have (dummy) data of People in a Google Sheet tab ('Sheet1') that looks like this (Name, Email ...
user avatar
  • 7
0 votes
1 answer
30 views

Way to keep spaces in Google App Script alert?

SpreadsheetApp.getUi().alert('foo bar') When I use this piece of code, it appears as 'foo bar', removing the additional spaces in between. is there a way coerce Google App Script to do otherwise?...
user avatar
  • 375
0 votes
1 answer
28 views

Sorting/Filtering Data to Show Only Checked Out Items

I have a Google Sheet that has ~5000 rows of items that I need to sort/filter by various means and I can't seem to wrap my brain around it. I've sorted them alphabetically but I feel like what I need ...
user avatar
0 votes
1 answer
20 views

Conditional formatting in Google sheets to indicate overages

To help identify issues faster I am trying to add conditional formatting for column Z to turn red if the amount in that column is greater than the amount in the columns AA or AB and only if column AA ...
user avatar
  • 3
0 votes
1 answer
53 views

SPARKLINE function with LOWCOLOR and YMIN doesn't get along very well

I am using the SPARKLINE function to display a bars graphic within a cell, using the optional parameters lowcolor and ymin. The idea behind lowcolor is to be able to identify the lowest value with a ...
user avatar
  • 35
-1 votes
1 answer
47 views

Count all cells with specific data between certain dates

I have a schedule of a team planned on multiple projects on certain dates. I'm looking for a formula that retrieves the amount of days (cells) on PROJECT-A until TODAY().
user avatar
0 votes
2 answers
37 views

Convert column with text into a drop down menu

I have a column filled with single text elements like "TestA", "TestB" and so on. I now want to convert this column with text into a drop down menu where the drop down is already ...
user avatar
  • 1
1 vote
1 answer
41 views

How do I group a new column by categories based on another column?

I would like a formula to essentially do what the =countif "criteria" formula does; however, instead of a number of how many times "criteria" occurs I would like it to display the ...
user avatar
  • 11
0 votes
1 answer
19 views

How to automatically insert datetime across multiple Google Sheet tabs

I have 6 tab Google Spreadsheet that is actively being updated with data into each individual tab. What I am looking for is to enter date-time stamp after each and every entry across all tabs. I have ...
user avatar

1
3 4
5
6 7
158