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
26 views

In Google Sheets, I'm having some difficulty getting two curves on the same axis

In Google Sheets, I'm having some difficulty getting two curves on the same axis. How do I get a set of axis with 2 sets of x,y values on it. I looked at 1 plot with several lines with a common X axis ...
user avatar
  • 2,623
0 votes
1 answer
26 views

How do I make the scale the same on these two graphs in Google Sheets?

How do I make the scale the same on these two graphs in Google Sheets? On the x axis is t/time, on the y axis is amplitude.. In the x/y on the second graph/table, I made those x values to be 2* the ...
user avatar
  • 2,623
0 votes
0 answers
18 views

When adding a row does/will the cell history always show a "Replaced:" history reference?

Following the user name, date, time stamp, I see this: Replaced: "=C512*D512" with "=C511*D511"
user avatar
  • 1
0 votes
3 answers
33 views

CountIF cells related to a date range (month)

I have a real estate company and have a spreadsheet that tabulates all the data on our closed deals. One column is the date that the property closed. I am trying to figure out a formula that ...
user avatar
0 votes
1 answer
25 views

Group occurrences and combine values for Line Chart

I want to create a chart that counts occurrences from two different columns which might not have the same values. Here is a sample data: Be aware that the unique values might not be the same in each ...
user avatar
  • 101
0 votes
1 answer
45 views

In Google Sheets, take the first and last value of a query to sumarice, grouped by user and shorted by date

:Edit -> added a working spreedsheet with the data https://docs.google.com/spreadsheets/d/1RNlZtvYZIpRF97u-7cOEGLS4my4OcLHD_1OJfDRCVCA/edit#gid=2100307022 I have a spreadsheet with a list of users,...
user avatar
0 votes
2 answers
35 views

Google Sheets removes decimals when formatted to plain text

I have texts that are in format 444444.444444, it is meant to be a plain text. Google Sheets detects it as number and shows only 0-1 decimals. I click Format -> Number -> Plain text, the result ...
user avatar
  • 101
0 votes
1 answer
52 views

Google Sheets - efficiently aggregating one column for grouping, based on minimum value in another column?

I have a sheet with upward of 1000 rows with the following schema: GroupCol | NameCol | NumberCol Example spreadsheet (much smaller data) In the dataset, any given value in GroupCol is repeated 1-10 ...
user avatar
1 vote
0 answers
23 views

In Google Spreadsheets how to avoid figures changing places

I am using a Google spreadsheet and I insert several images. Since I want to point some places in the image I use a figure (sorry the names are all in Japanese so I don't know if I am using the right ...
user avatar
0 votes
0 answers
17 views

Is it possible to make a filter and search formula that imitates the Filter feature in Google Sheets?

I am making a database system using Google Sheets. I have successfully used the filter and search formulae but my problem is it only displays the data and it is not editable. I am looking for a ...
user avatar
0 votes
1 answer
25 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
25 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
41 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
22 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
18 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
71 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
43 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
31 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
17 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
127 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
89 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
36 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
40 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
39 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
0 votes
1 answer
44 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
53 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
25 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
26 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
25 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