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.
8,668
questions
0
votes
0
answers
5
views
What causes Google Sheets FILTER() range mismatch? [closed]
This formula
=FILTER(dJournal!$A$5:I,dJournal!$B$5:B>"1")
gives me this error:
FILTER has mismatched range sizes.
Expected row count: 998. column count: 1.
Actual row count: 1, column ...
- 101
0
votes
0
answers
5
views
Wrong grand total when using Calculated Field [closed]
I have a simple pivot table, but I'm using Calculated Field values.
I don't know why the Grand Total of the CURR POSITION column shows a wrong value. It shows $3,044.75 instead of $1,150.88
How can I ...
- 103
0
votes
1
answer
4
views
Trying to round a number to the nearest millions
I have several hundred rows of revenue data that start from row 1 and goes all the way down the sheet in column A. Something that look like this...
A
B
$135,345,000
$200,000,130
$383,430,000
$4,...
- 3
0
votes
0
answers
7
views
Applying Conditional Formatting to LAMBDA-MATCH Function [closed]
I've used a LAMBDA-MATCH joint function in Google Sheets to populate a data table. I'm looking to format each row of the table based on the value of a column, "Start date", whereby rows ...
- 225
0
votes
1
answer
11
views
How to Count Combinations In a Set (Including dupes)
Trying to figure out a formula where I have 5 columns of text data. I want to count how many times combinations of data in 2 contiguous columns appear in each row.
Example data:
A
B
C
D
E
1
A
A
A
B
...
-1
votes
0
answers
9
views
How to hide row numbers and collumns header in a Google Sheets? [closed]
asked 1000+1 times and still nothing? Yes, I wanted to comment on the previous one, but I'm new and can't. I hope I can post this request
0
votes
1
answer
5
views
in google sheets, automatically sum up sales between dates
In Google Sheets, how can I automatically sum up the sales from a particular area from the start date to end date (sample: March 3 to 10 or March 2 to 15)? Is it possible to just type the area and the ...
0
votes
1
answer
13
views
SUMIF sometimes not summing when criteria is met
SUMIF is not summing some of the cells despite the logic being met. For example, cell B9 on sheet 3 should result in 25, but it is coming back with 0.
I've tried
sumif(Sheet2!$B26:$O26, "*MW*&...
- 1
1
vote
1
answer
20
views
Change colors on graph
I have a graph on google sheets, and want to change the background color for certain values on x axis. For example, from a value A to a value B (on x axis) I want a background color 1, then from ...
- 11
-1
votes
0
answers
9
views
Why does google sheets move the cursor left when type "*" after i selected a cell? [closed]
I am multiplying a cell by something, and if I type "*" just after I selected a cell, the cursor moves left randomly, so I have to press the right arrow after the *.
0
votes
1
answer
13
views
Add hourly rate to line items based on line item date
Hello, I'm trying to set up a system using google forms and google sheets to keep track and analyse my business's employee's hrs and wage.
My idea is:
Use a simple Google Form to for employees to ...
- 1
0
votes
0
answers
9
views
Too delayed execution of Googld Apps Script Trigger [closed]
I'm trying to clear a sheet of my total spreadsheet every 1 hour.
It is obviously very simple script,
just find corresponding sheet name (SkullKing), and clear certain sections.
function ...
0
votes
1
answer
12
views
on google sheets, use "this line" instead of line number [duplicate]
I have the following formula on a google sheet:
IFERROR(Vlookup(A3,INDIRECT(A$1),1,false),0)
My desire is to have this formula use A3 on line 3, A4 on line 4 and so on
However, sometimes I get it ...
- 277
0
votes
0
answers
15
views
Cell to Row then Cell to column
There's a way to add column and add data after we split from cell to column using Apps Script?
Currently I split my cell to a new row
from this
to this
using this code
function result(range) {
...
- 1
0
votes
0
answers
17
views
Google Sheets formula: to search for text string in a cell and reference a table to find text string and corresponding item [closed]
I'm trying to set up a formula in Google Sheets where it can search for multiple strings in a cell, then reference a table where each string is tied to a certain text. The cells won't have more than ...
- 1
0
votes
1
answer
37
views
Return Value to Different Line with Possible Duplicate Information
In Google Sheets, I need to extract values of D in specific rows and insert them into column I where appropriate.
See link below. Here is an explanation of what I have so far in the spreadsheet:
Raw ...
- 15
0
votes
0
answers
10
views
How to calculate a reverse exponential graph? LN function? Log Function? [duplicate]
I have a lot of revenue data across thousands of companies down row A below (for this example I'm showing just 3). We are trying to understand a company's "Total Addressable Market" (TAM) ...
- 3
0
votes
1
answer
16
views
SUMIF for unique entries
I need to do a SUMIFS statement in Google Sheets but am having a hard time not double counting duplicates in the data. For example:
Volume
Flavor
Batch #
6000
CLM
BT1000
6500
CLM
BT1000
7000
WBG
...
- 3
0
votes
0
answers
15
views
Search column H for non-blank cells and return the value in column A [closed]
I am trying to search the top chart, column H (H4:H24), for non-blank cells, and return the corresponding value in column A (A4:A24), in the bottom chart, column H3:H7. Basically I want to search ...
- 1
0
votes
0
answers
8
views
How to stop Google Sheets from defaulting to AM when inputting time [closed]
I have scoured the internet trying to find an answer on how to fix this, and maybe I can't. I am trying to make a labor log for an auto shop I work for, and I want them to be able to input their start ...
- 1
0
votes
0
answers
20
views
Trying to get the opposite of an exponential result [closed]
I have a lot of revenue data across thousands of companies down row A below (for this example I'm showing just 3). We are trying to understand a company's "Total Addressable Market" (TAM) ...
- 3
0
votes
1
answer
25
views
simplification of a fomula using offset
I want to use a less complicated formula to achieve
the answer to cell D28 in this sample spreadsheet
I was wondering if there was a way to group the offset cells together in a set so I didn't have to ...
- 95
0
votes
1
answer
14
views
Have multiple selections in drop down Google Sheets
I'm trying to make it so that I can select two types of membership for one individual (e.g. volunteer and family).
- 3
0
votes
1
answer
20
views
Google Sheets: Splitting siblings by different @class attributes into columns using IMPORTXML formula
Good day, everyone! ^^
I am still a beginner in Google Sheets formulas and still don't understand many things, but I am trying to scrape sample sentences from an online Japanese dictionary using ...
- 55
0
votes
0
answers
25
views
Google Sheets / IMPORTjson(): How to Resolve "Invalid Argument" Error [closed]
Using the Google Sheets IMPORTjson AppScript function, I'm try to pass this long URL and keep getting the error "Exception: Invalid argument:" as a response.
The formula I'm using is =...
- 225
-1
votes
1
answer
19
views
"HOW TO KEEP FORMATTING ON A SPECIFIC CELL/ROW (USING SORT FUNCTION) AFTER NEW DATA IS ENTERED"
I'd really appreciate some help with the document.
I'm using the auto sort (SORT function) on the second sheet and would like to keep the conditional formatting to a certain cell/row after a box is ...
- 1
0
votes
0
answers
26
views
VLOOKUP or INDEX/MATCH used on a HTML Import [closed]
A little background, some friends and I wanted to start a Fantasy Golf league. To start we would pick our teams and fill in manually. I did some research and started to try and automate the process. ...
0
votes
1
answer
18
views
Help getting multiple rows of data from a selected drop down list
I have the following rows of data in my sheet. Here's 7 as an example, but I essentially have over 1000 rows of data.
Column A
Column B
1
Tesla
Jess
2
Toyota
Kelly
3
GoPro
Jess
4
Apple
Linda
5
...
- 3
0
votes
0
answers
9
views
How to return only certain date ranges from source data? [closed]
=query(IMPORTHTML("https://walletinvestor.com/bse-stock-forecast/tata-elxsi-ltd-bse-prediction/data","table",1),"Select Col2,Col3")
Above Googlesheet Query Gets me the ...
0
votes
0
answers
18
views
How to Automatically Format a Google Sheet from Google Form data?
I have a Google Form which is linked to a Google Sheet that is submitting data for warranty exchanges for a client. At the end of each month they download the sheet as an Excel file to enter into ...
- 1
0
votes
1
answer
6
views
Exception: The parameters (String) don't match the method signature for SpreadsheetApp.Spreadsheet.getSheetNam [closed]
I am trying to create a google sheets form that would automatically populate the entered data from one sheet to another within the same document. However, I am getting this error when running them. ...
0
votes
0
answers
12
views
Reference other sheets in Google Sheets using file name? [duplicate]
I have a set of Google Sheets workbooks in a Google Drive folder that reference each other. One of the source workbooks contains data that is externally updated daily which then replaces the same ...
user1779906
0
votes
0
answers
13
views
How to use an arrayformula with dynamic row numbers (and other nested functions)? [closed]
I have 9 columns, each with the header 1-9. I want to iterate across each row, and if value exists in a particular column, I would like to grab the header value (1-9) and concatenate it as a comma ...
- 1
1
vote
1
answer
34
views
Cannot find active sheet: TypeError: read properties of undefined (reading 'source')
I'm trying to add the current date to a cell in column K when any cell in that respective row is changed.
I found this script and altered it for my situation so I can deploy it as a trigger:
function ...
- 415
1
vote
1
answer
45
views
Why are the share button and toolbar blue in Google Sheets? [closed]
Here is the capture.
But the share button and toolbar look normal if I use another account.
- 11
0
votes
1
answer
14
views
Insert formula, process and replace by raw value
What is the best approach to achieve the following:
If a cell in column A has content, then place a formula into the cell in column B in the same row, referencing the cell in column A.
Once the ...
- 5,266
0
votes
1
answer
12
views
Google sheets. Compare the value in column on the 1st sheet with 2 others and then pull cells to y the right of matching value in the other sheets
I have one workbook, 3 sheets. Ultimately I'd like information from sheet 2 and 3 joined with corresponding information on the 1st sheet.
The 1st column has an ID number on each page. How can I make ...
Ariceliz
0
votes
1
answer
41
views
Google Sheets Pivot Table - Show totals for only one column in grand total
I have a pivot table that has 1 Row (ATIVO), 0 Column and 4 Values columns (Quantidade, Preço Un. Atual, Preço Médio and Total).
But I only need the grand total (shown as Total geral) from the last ...
- 103
2
votes
1
answer
55
views
Append Current Stock Stock Price to Historical Prices
I'm looking to append the current share price of a stock to the end of a period with changing durations.
My current formula for the historical prices is (where cell B3 is a duration of days to ...
- 225
1
vote
1
answer
22
views
How to average the last 5 nonblank, nonzero value in a row in Google Sheets?
I run a disc golf league and in order to track handicaps we take an average of the last 5 rounds played. I currently have the player name in column A, the handicap in B, # of rounds played in C, best ...
- 13
0
votes
1
answer
32
views
Is there a way to search backward in Google Spreadsheets?
When I click CTRL+H then I can open big search box in Google Spreadsheets. It searches forward for specific text all sheets from the position of my cursor. But is there a way to search backward?
I ...
- 101
1
vote
1
answer
38
views
Differentiating between 0 and empty cell with VLOOKUP in Google Sheets
I am trying to use VLOOKUP in this Google Sheet to convert percentage grades to a 1–8 grading scale. I would like to be able to differentiate between a score of 0 and an empty score but am having ...
- 113
2
votes
1
answer
173
views
How to create a custom number format in Google Sheets that handles single and multiple digit numbers differently
How can I create a custom number format in Google Sheets that displays numbers > -10 and < 10 to one decimal place, but displays numbers <= -10 or >= 10 with no decimal places? The format ...
- 95
0
votes
1
answer
26
views
How do I hide a column in Google Sheets from another user with a password? [duplicate]
I have a sheet being used by 4 different persons;
one of the columns has private information, related with payments.
I want to be able to see it all the time, but I don't want them to see it, even if ...
-1
votes
1
answer
19
views
Use IMPORTDATA with URL and NOW() [closed]
I am trying to use IMPORTDATA in GoogleSheet with URL and time stamp
=IMPORTDATA("https://www.amfiindia.com/spages/NAVAll.txt?t"=TEXT(NOW(),"ddmmyyyhhmmss"))
ERROR This function ...
-1
votes
1
answer
15
views
How do I add a dollar sign to the left in Google Sheets?
I've seen the previous answer
Highlight the cells you wish to be this way then click "Format" -> "Number" -> "Accounting". Well, why to go to that trouble when the $...
- 7
0
votes
1
answer
22
views
With vanilla functions, detect if number is *formatted* as percent?
A have a numerical field with a custom number format which should accept a number (decimal between negative and positive infinity).
When a user wishes to, they may write something like "45%"...
- 303
0
votes
1
answer
20
views
Pulling data from Google Sheets tab and placing them into new tab but into the correct section for that month
I have a Google Sheet containing sales information; this data is inputted into a sheets tab where staff list common sale items: Client Name, Date, Month, and Invoice Amount.
My current Google Sheet (...
- 11
1
vote
1
answer
29
views
How do I get Google Sheets to choose the winner of a sports game, based on each team's score?
I am doing a report on whether Expected Goals (xG) is an accurate predictor of the winner of a soccer game. I have every game from the 2021-2022 English Premier League Season organized into columns. I ...
Carter Sasser
-1
votes
0
answers
23
views
Conditional formatting first working day of the week
How would I conditionally format a cell within a range (I2:I260) to fill with a colour when the first workday of the week appears. Sometimes due to public holidays Tuesday would be the first workday ...
- 9