Skip to main content

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 data as markdown table 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
12 views

How do I make Shift+space select the full row in Google Sheets, not only a part of if? [duplicate]

I select a cell: I press SHIFT+Space. I expect the full row to be selected. But only a part of it is selected: If I press Space a second time without releaseing SHIFT it really selects all the row. ...
Xavi Montero's user avatar
0 votes
0 answers
12 views

Google sheet ROI formula not working

My Google sheet formula isn't working as intended. In order to calculate the ROI of an event I'm making I use different stats and make a big calculation of points in the ROI column The formula I'm ...
Maslie's user avatar
  • 1
0 votes
1 answer
15 views

Conditional formatting based on partial text in another cell

I have a google sheet, I want each cell in column B to change colour if the cell on the same row column F includes the phrase "RF FTE". It may also have other data in that cell but if "...
Pete's user avatar
  • 1
0 votes
0 answers
6 views

Automate data referral from another sheet [closed]

I'm trying to reference data from another sheet into a 'calculation' sort of sheet, which is easy enough, but I would like new data brought into the first sheet from the rest of the team to be ...
Riccardo's user avatar
0 votes
2 answers
20 views

Shift space does not select the full row in Google Sheets, only a part of it

In Google Sheets, if I select a cell and then SHIFT+Space I expect the entire row row to be selected however sometimes only part of the row is selected and I need to press SHIFT+Space again. For ...
Xavi Montero's user avatar
0 votes
0 answers
8 views

How can I conditionally format cells based on times that span days?

In Google Sheets, I'm trying to set cell background colour using conditional formatting based on the time of the day compared to the time value in the cells. For example, I want to set a different ...
Patricia Stephenson's user avatar
-1 votes
0 answers
13 views

Automatically remove duplicates based upon semi reused Unique Numbers and date leaving the latest dated items

I suppose you hear this all the time but here I go again "I'm new to Google Sheets" :-) Apologies for asking what might be basic questions! So, I have a google sheet that is populated by a ...
Clever Dog Production's user avatar
-1 votes
0 answers
6 views

Trying to Countif Color of Cell matches

I'm currently using the following code: function countColoredCells(countRange,colorRef) { var activeRange = SpreadsheetApp.getActiveRange(); var activeSheet = SpreadsheetApp.getActiveSheet(); ...
Stephanie Keffton's user avatar
0 votes
0 answers
17 views

Spaces being incorrectly removed from text strings when importing data into Sheets

I have spreadsheet data that I want to import to Google Sheets. The source spreadsheet is a proprietary format; neither Google Sheets nor MS Excel. The issue I'm running into is that I have columns ...
Haris Serdarevic's user avatar
0 votes
0 answers
14 views

Using an IF statement in Google Sheets to pull data across worksheets [closed]

I am trying to filter by labels across worksheets. In worksheet 1, if something is labeled "Label 1" in the F column, I need it to populate in Worksheet 2 in column A. I also need if ...
user avatar
1 vote
1 answer
16 views

I can sort normally using mouse, but keyboard shortcut gives error

When I select a cell range and go to Data>Sort Range>A to Z, it works perfectly! but when I select a cell range and press Alt + D + S + A, then it gives me an error that few of my cells are ...
dudeAwesome's user avatar
-1 votes
0 answers
12 views

Need change Google Sheets to Google Drive Script to make sure the downloaded image has an extension included

This is a script I created using ChatGPT and it works but there is a small issue. The script checks a column called "downimg" to see if it contains the text "stop", if it does, the ...
itoldusoandso's user avatar
0 votes
2 answers
33 views

Calculate the duration in weeks that it would take to lose a specified amount of body weight (BW) givent 1% of body weight is lost exactly each week?

Calculate the duration in weeks that it would take to lose a specified amount of body weight (BW), in pounds, given that 1% of body weight is lost exactly each week? I would like to be able to find ...
Dal's user avatar
  • 5
1 vote
2 answers
42 views

How to divide really big numbers in Google Sheets

As part of the creation of an IBAN number generator, I need to divide a 26-digit number by the number 97 to find out the check digits in the IBAN number. After about an hour of searching, I found out ...
Ogi22's user avatar
  • 9
0 votes
0 answers
18 views

Historical crypto prices into Google sheets [closed]

I would like to print the lowest price of a crypto coin / token in a given year using the importdata function. I came up with this: =IMPORTDATA("https://cryptoprices.cc/BTC/") I trid to add ...
user avatar
0 votes
1 answer
19 views

onEdit function to clear range of cells

I need some help to create a simple trigger for my Google spreadsheet. What I'd like is to enter "clear" in cell D1 and have it triggers a script that clears all the cells in the range B2:...
Colin's user avatar
  • 11
0 votes
0 answers
8 views

Copy different text than what's visible in the cell [closed]

I doubt that this is possible, but I figured I'd ask just in case. I want a cell to show specific text but when I copy & paste it'll paste different text. So when I look at the cell it'll say &...
user avatar
0 votes
1 answer
27 views

How to dynamically return values horizontally in Google Sheets

Say I have a column like this: user item User1 Item1 User1 Item2 User1 Item3 User1 Item4 User1 Item5 User2 Item1 User3 Item1 How can I return them like this in a filter/lookup formula: user ...
yesnomaybe's user avatar
0 votes
1 answer
27 views

Sum multiple currencies by looking up conversion rates based on multiple cell values

I'm trying to sum values that are in different currencies. The currency for each value is specified in the previous column and the final result should be in Australian dollars AUD. I use a fixed ...
Phil's user avatar
  • 3
0 votes
1 answer
31 views

Join results of a BYROW function that expands into multiple rows

I have two sheets, one containing user-entered data, and a second containing a bunch of data. I have included example sheets at the bottom of this question. I'm trying to build a formula that for each ...
Spedwards's user avatar
  • 215
0 votes
0 answers
25 views

Formula result not displaying in google sheets cell [closed]

Sheet A is called 'Cash Flow Table' which is a pivot table. In Sheet B, cell A1, I have ='Cash Flow Table'!P48. Cash Flow Table cell P48 contains the value $10,674.27. On Cell A1 in Sheet B nothing ...
Bill Adams's user avatar
0 votes
1 answer
22 views

How to force English number format for all number values in a column?

This is what I see by default in my table:  Pasted  Value    DisplayedValue                                                                                                                             ...
Tomáš Zato's user avatar
0 votes
1 answer
16 views

Sorting by date on a master spreadsheet with data imported from other spreadsheets

I have 4 spreadsheets importing data from cells A2:V, each spreadsheet auto sorts on edit by date (column 17), but when I ask the master sheet to auto sort by column 17, it sorts them by date but also ...
Psych-out's user avatar
-1 votes
1 answer
18 views

How to get a cell to autodelete contents after subtracting from another cell

I have a spreadsheet to keep track of payments on group trips and would like to be able to keep everything within that spreadsheet. I would like the ability to be able to delete from the sum that ...
Yager's user avatar
  • 1
0 votes
2 answers
27 views

How for each cell in a row return and concatenate multiple matches without dragging down formulas?

I have a list of items and in another place I have list that covers some of these items each time with a different key. How to fill in column B all matching results next to the original item (column A)...
LWC's user avatar
  • 1,230
-3 votes
1 answer
29 views

More computational capabilities per spreadsheet

I am a pro user of Google Sheets and Google Apps Script. Is there any possibility to get more computing capability per spreadsheet? Some of my spreadsheets become unusable due to a large amount of ...
Piotr Magdziak's user avatar
2 votes
1 answer
127 views

Google Sheets: how to format currency with numbers right-aligned but +/- and $ left aligned and padded with spaces?

I've already looked at: How to left align currency symbol but right align number? How to left align dollar symbol but right align number? The best number format I have so far is: [$$]* #,##0 which ...
Ryan's user avatar
  • 774
-1 votes
1 answer
26 views

Growth between 3 cells in Google Sheets

I am creating a data wall to compare student achievement across 3 time periods Beginning of the Year(H2), Middle of the Year (I2) and End of the Year(J2) and having the difference recorded in K2. I ...
Justin Barry's user avatar
1 vote
2 answers
39 views

I'm looking for a way to return multiple values in an array formula on Google Sheets - haven't been able to find a solution

Scenario: I have a roster of dog owners who have had dogs involved in various training classes. I have a list of the dogs and their respective classes, and I have a list of the owners. I want to ...
Alison's user avatar
  • 11
0 votes
1 answer
16 views

Conditional formatting formula to highlight duplicates but only if the date equals today

I'm trying to write a conditional formatting rule to highlight cells in B3:B that are duplicates, but only if today's date is in column A. I started with =COUNTIF(B3:B,B3)>1 which returned the ...
Jeremy Urekew's user avatar
0 votes
2 answers
35 views

How to use the value of a cell to find another cell?

I’m trying to create my own data sheets for reading at school but when I put the reading ages in, I need it to select the zpd level and the reading targets automatically base on what I input. Eg if I ...
Kevin Wilson's user avatar
0 votes
1 answer
22 views

Show zero value if result is NA in query google sheet

It showed #N/A if "Cash" is not in list. How can I show result "Cash is: 0" instead of #NA?
Sok Dara's user avatar
0 votes
2 answers
27 views

What's the fastest way I can open the Google folder in which the spreadsheet is located?

I have opened a Google spreadsheet. What's the fastest way I can open the Google folder in which the spreadsheet is located? Fastest way means with the fewest clicks.
Franck Dernoncourt's user avatar
0 votes
2 answers
17 views

Sort data by number in cell containing number sometimes followed by text

I'm working on a shopping list with aisle numbers in Google Sheets and the aisle numbers can be digits alone or digits followed by a letter. For example 1, 2, 3, 3, 4, 6A, 8, 19B, etc I want to sort ...
JeffK627's user avatar
-1 votes
1 answer
28 views

Breaking up long column array into different columns

I want to be able to copy and paste an order of draw (starts with the draw number and school name, then each proceeding row is the athletes names) which is just a long array in a single column and ...
AnonymousTurtle's user avatar
0 votes
1 answer
36 views

How to use Array with Sumproduct or other formula

I am trying to use an array formula like SUMPRODUCT to perform a lookup with 2 criteria. I have a sheet named data that stores customer-specific pricing for products. A B C             D 1 ...
Anakin Teow's user avatar
0 votes
0 answers
10 views

MORE DETAILED explanation of ERROR: Array result was not expanded because it would overwrite data in B6 [duplicate]

Here are screenshots from my spreadsheet that has the issues I discussed in the previous question: IMPORTRANGE(,) works in a single cell, however, when adding the same formula to the next cell in the ...
Newton's cat's user avatar
-2 votes
2 answers
47 views

Formula for adding value in each cycle for compound interest

In Google Sheets, I know the formula for compound interest, for example: 10000*(1+10%)^n 10000: start-up cash 10%: interest rate n: interest cycle But what if I also add 10000 cash each month? What ...
Yu Lun CHEN's user avatar
0 votes
1 answer
18 views

How to sum an answer tracing upwards until it hits an positive value?

Thx for coming in! I need a formula to trace and sum the value in a column, upwards, until it hit an positive value. For the following table, A will have the answers, and B will be the column where I ...
Yu Lun CHEN's user avatar
-2 votes
1 answer
194 views

Solving ERROR: Array result was not expanded because it would overwrite data in B6 when using IMPORTRANGE

I am trying to make a Google Sheets book to keep track of my schedule. When importing data across sheets, I used IMPORTRANGE(url, range) to import data from Sheet1 to Sheet3. This worked at first; ...
Newton's cat's user avatar
1 vote
2 answers
27 views

Importrange Query importing correct OR statements, but not refining by date

I have a formula pulling relevant columns from a different document. It is working well except for the last part regarding the dates. It is seemingly ignoring that part of the formula and returning ...
Salt_Tea's user avatar
1 vote
2 answers
32 views

Count based on drop-down selection criteria

I have a sample dataset similar to the screenshot below in column C:E. I am trying to have a formula that will return the total count (B9) based on the dropdown selection in A2. If 'All Items' is ...
Elaine's user avatar
  • 43
1 vote
1 answer
25 views

How do I set up conditional formatting for values that contain text from a cell in another tab?

From similar problems I found online, I've tried a number of regexmatch and combined it with indirect but nothing seems to work. Here's a sample of my sheet: https://docs.google.com/spreadsheets/d/...
Jessica Gutowski's user avatar
1 vote
1 answer
15 views

Return the corresponding pay and revenue values based on the range of numbers that includes a lookup value

If using data similar to below, How can I have it so that a lookup number included within a number range returns the corresponding pay and revenue? Data Example: Miles Revenue Pay 0-55 200 ...
Melanie Huer's user avatar
1 vote
2 answers
36 views

What is the syntax for an indefinite range? [duplicate]

In Google Sheets formulas I often see ranges specified with a large terminating number, such as B3:B299 = SUM(B3:B299) The upper-bound, or largest row number, is often chosen to be large enough to ...
Theodore Shepard's user avatar
3 votes
2 answers
71 views

Google Sheets Formula to find specific months

I have a Google Sheets document with multiple sheets. What I want to do is perform a lookup on a specific sheet Expenses, and find all the expenses for a given month and year. Here's what I have so ...
jbuck's user avatar
  • 31
0 votes
1 answer
34 views

How to strikethrough a text if the text is the same in another cell using conditional formatting?

I am making a table assignment for the guests for my brother's wedding. I want to know if it's possible to conditionally format to strikethrough a cell that has the same text as another cell. In this ...
user326450's user avatar
0 votes
1 answer
30 views

Macro to return 1 + the value of the cell above

I am experienced with VBA but not Google Apps Script / Javascript. I'm doing a lot of data entry for which filling down with CTRL+D is super-useful but I want to create a macro that will copy down the ...
Daniel Austin's user avatar
0 votes
1 answer
28 views

How to use if contains within an arrayformula and multiple or conditions?

I want to replace the "=" in the following example with a text contain to match entries partially instead of entirely. However, I cannot get this to work. I thought I could replace the (A2:A=...
orschiro's user avatar
  • 5,518
1 vote
2 answers
50 views

Replace text in document using A1 notation as variable names in order to retrieve values stored in those cell references

I have a Google Doc that contains placeholders based on A1 notation like %A1%, %B3%, %A3%, and so on. In a Google Sheet I have values stored in the cell references that match the variable names. I ...
user61681's user avatar
  • 113

1
2 3 4 5
179