12,259 questions
0
votes
1
answer
33
views
Plotting a cell's value in sheets as if another cell is changing
I have a spreadsheet with some calculations in it, and I want to make a plot of what happens to one cell's value as another changes (from 0 to 100). I have been searching for about 45 minutes and cant ...
- 748
0
votes
0
answers
41
views
Why does INDEX-MATCH fail with named ranges but work with cell references in Google Sheets?
I'm using Google Sheets and trying to create a formula that retrieves a value based on a match in a named range. The INDEX-MATCH function works correctly when I use cell references but returns ...
1
vote
0
answers
23
views
Google sheets equivalent of Explode in python? [duplicate]
I have a table with one of the columns containing a list of comma delimited values in google sheets. How do I transform each element of a list-like to a row, replicating index values.
Python ...
-1
votes
1
answer
87
views
Excel getting names from biggest values with Regextract
I have a small problem with my Excel project and I can't find way how to make it true.
Current formula:
=INDEX(A2:A51; MATCH(LARGE(Value(Iferror(REGEXEXTRACT(split(C2:C51; "-"); "[0-9]+&...
- 13
0
votes
1
answer
47
views
How to use a query within a query?
As a fitness trainer I have 2 sheets in Google Sheets to track invoiced and consumed training sessions:
Sheet 'CalendarEvents'
source customerId customerName eventType eventName ...
- 14.6k
1
vote
2
answers
83
views
Map a table, replacing values based on other cell
I would like to map a table replacing some data based on data from another cell..
In my below example I would like to remove data from the "Status" column if the cell to the left on the &...
- 31
1
vote
1
answer
102
views
Generating date range based on allowed weekdays, repeat times and times of day in Google Sheets
Recently, I’ve been dealing with a lot of medical-related things. To make it more manageable, I decided to create a schedule that I could print out, follow, and update as needed.
Input Data
Time of ...
- 13
0
votes
1
answer
59
views
How to sum up multiple VLOOKUP results?
I've got two lists: one with items and one with orders
Item
Price
1
10.10
2
2.02
Order
Item 1
Item 2
...
Item 15
Sum
A
1
10.10
B
1
2
12.12
For each order I want to sum up the prices of the ...
- 1,948
1
vote
1
answer
57
views
Issue Google Sheets Query: Auto expand rows on protected sheets with QUERY function
If a user only only have edit access to specific cells, the user cannot add rows to the sheet and so the QUERY function cannot auto expand needed rows on ends up with an #REF error.
Output: #REF - ...
2
votes
1
answer
73
views
Why do I get the error SPLIT parameter 1 empty error when it isn't?
I'm using the formula below to divide numbers in the format (EX:2/4/6/8) from column E by the number in column D (EX: 2) and provide output in the same format from column E (1/2/3/4).
=ARRAYFORMULA(IF(...
- 23
0
votes
1
answer
55
views
Filter data using multiple sets of checkboxes to determine the criteria
I am trying filter data using multiple sets of checkboxes to determine the criteria.
Here is the spreadsheet I am working on. Here are some notes about each sheet:
Dashboard: I have four sets of ...
0
votes
1
answer
61
views
How can we sort multiple values horizontally using VLOOKUP?
We send inspectors to stores once or maximum twice a month and we have a store scoring system.
Audit information is written on the home page, including the date on which the auditor went and where the ...
- 45
-5
votes
2
answers
69
views
COUNTIFS WITHIN DATE RANGE GOOGLE SHEET
I need to get the count of my NOTICE SENT weekly. I have updated as of date to identify the week I need in the report.
FORMULA:
=COUNTIFS($B$3:$B$8,$F$2,$C$3:$C$8,$F3,$D$3:$D$8,">="&$...
0
votes
1
answer
39
views
Number of days between two dates excluding Sunday in Google Sheets
For example, there are 14 days in total between 02.12.2024 and 16.12.2024 (excluding 02.12.2024, including 16.12.2024), but there are only 12 days excluding Sundays.
How can I calculate the number of ...
- 45
1
vote
2
answers
81
views
Pickup n columns following a pattern
I try to find a nice way to select columns following a specific pattern.. the columns I want appear in my example every 3 columns (Col2, Col5, Col8)
Start Data
kwh
Price
Price per kwh
kwh
Price
Price ...
- 31
-1
votes
1
answer
70
views
Google Sheets Translations
I am translating a google sheet from English to Italian (and other languages) I have got the translation working, but I need to restricted the translation to 200 characters per translation, due to ...
0
votes
3
answers
58
views
SUM on google sheet always returns 0
The sum on my spreadsheet column Category always returns 0.
I have formatted column Amount as Number but still it doesn't work. I had also removed all currency symbols from that column using find &...
0
votes
1
answer
58
views
Google Script To Copy row based on Drop down, Not working
I am trying to copy the entire row (From the calls tab) if certain items are selected in the J column. Basically if any of the items are selected in the drop down menu of the J column except "...
1
vote
3
answers
66
views
How do I do basic date grouping operations in Google Sheets?
I have data that looks like this (sample):
Col A
Col B
Col C
Value 1
Value 2
1/4/24 4:15 pm
Value 3
Value 4
1/4/24 6:30 pm
Value 5
Value 6
1/5/24 2:30 pm
Value 7
Value 8
1/5/24 3:30 pm
Value 9
...
- 819
0
votes
1
answer
69
views
Google Sheets Formula to Clean An Array of Data
I have data related to product code, product name, and item name with the following data example:
Product Code
Product Name
Item Name
01
A001
Primary
-
Primary
Book
Primary
Fruit
Primary
-
01
...
- 57
-3
votes
0
answers
60
views
Excel or Sheets : Extract unique values [duplicate]
Data table
I have the left table with various values for each name.
I want to have the right table, so that the name gets repeated as many as the number of its existing groups.
In fact, the second ...
0
votes
1
answer
78
views
How to unnest data from one column to fit the entire dataset - Google Sheets
I'm trying to unnest data in one column separated by " | ". The rest of the columns should "follow" the unnested logic from that column.
I've made a sample spreadsheet to summarise ...
- 365
0
votes
1
answer
38
views
How to filter Data from 2 different sheets via Date
How can I count the Datas from different dates and different reasons for denial from one sheet to another sheet. Here's the information with the sheets
I used this code but won't work :
=COUNTIFS(...
1
vote
0
answers
45
views
Custom Conditional Formatting Rule Isn't Valid [duplicate]
I have a list of dates on one sheet, a range named "CalDates" that are as follows:
8/19/2023
8/26/2023
9/2/2023
9/16/2023
9/23/2023
I then have a calendar on another page with numbers for ...
- 383
-5
votes
0
answers
98
views
Create every unique permutation from a list in google sheets [closed]
I need help displaying some data in google sheets.
I have a range of people, and groups. Let's say it's A, B, C for now to represent the people. In practice, there could be any number of people, and ...
- 15
1
vote
2
answers
62
views
How to use COUNTUNIQUEIFS in Goole sheets with 2 conditions referencing cells
For example I have this 3 sheets:
SHEET 1 : EVENTS (Have all the info of an event)
EVENT
TYPES
BRANDS
1
3
B1;B2;B3;B7
SHEET 2: EVENTS AND BRANDS
EVENT
BRAND
1
B1
1
B2
1
B3
1
B7
SHEET 3: ...
0
votes
1
answer
42
views
Get highest occurring row number of key?
I'm making a google sheet to calculate ratings between teams that play against each other.
I know I can use the match function to get the index in a column from a key. I made a formula To find the ...
- 333
-1
votes
1
answer
57
views
Is there a way to make an updated list of all the required ingredients in google sheets?
I am trying to make a list of all of the recipes in Stardew Valley, what ingredients are required for each of them, how the recipe is unlocked, and what ingredients are needed for the recipes I haven'...
0
votes
1
answer
43
views
How to optimize / speed up calculation time with a lot of formulas? [closed]
I'm currently using google sheets to extract biographies from instagram usernames and I'm doing it for around 2500-3000 profiles a day and it takes around 18-20 hours which is a lot of time. When I ...
1
vote
1
answer
53
views
Rank based on which column is being sorted by in a Table
I have the following columns in a Google Sheets Table. In column A, I have the rank column.
I would like to have the rank value be based on whatever column is currently being sorted by. In this case, ...
- 2,915
0
votes
1
answer
60
views
Can a label in a Google Sheets Query be dynamic; can it reference a variable?
In QUERY, labels can be created with a text value. Example: label Col2 'Total'
This will display "Total" instead of the existing label for Column 2.
But what if the query uses a dropdown as ...
- 7,791
1
vote
2
answers
61
views
Return Column Based on Column Headers
I am trying to return a column based on column headers. The data displayed is dynamic, based on a dropdown selection and the header names may thus be in different columns.
My data headers are in ...
- 1,082
0
votes
2
answers
82
views
Fix Google Sheets formula so it will append the header title of each column it is pulling data from, instead of just the first column's title
I have a Google Sheet formula designed to pull the top 5 values out of a range of columns, display each of them separated by columns, calculate them as a percentage of a reference column, and append ...
- 11
-1
votes
1
answer
42
views
VLOOKUP IMPORTRANGE to select the column number in another file also
I want to vlookup importrange the data in another file and the specific column number will be selected in the another file because if ever there are changes in the column/field in the main file if ...
0
votes
1
answer
52
views
Getting unique letters from a word, in order, in a spreadsheet
I have a cell in a Google Sheet containing a single word. Let's say that word is TRILLION.
I want to obtain the set of letters in the word, in order of first appearance. For that word, the answer ...
- 96.3k
1
vote
0
answers
70
views
Is there a way to generate the authorization url for the importdata function to run?
I have a script set up to generate a spreadsheet we use when a new file is created in a particular folder. It uses the importdata() function in google sheets.
Recently google sheets updated their ...
- 565
0
votes
2
answers
96
views
Find entire list of most common values in a range?
I'm working on a Google Sheet that will include rows where people give a number rating then include text. I want to have a column where I read the rows, parse them into the number and text, then write ...
0
votes
1
answer
31
views
ARRAYFORMULA in GOOGLE SHEETS
I am trying to count sequentially depends on the value using ArrayFormula to avoid manually dragging the column, I want to autofill everytime I add a value in Column A
in Column B - the ArrayFormula ...
- 17
0
votes
2
answers
76
views
Split cell with dates and find matching dates on headers
I have a dataset that looks like this:
I put together a shared sheet here.
The formula currently in column E works like this... Count IF:
Has a "Yes" in columns F and beyond
Is greater ...
- 776
0
votes
0
answers
16
views
using Multiple IFs on Google sheets
I am trying to do addition on a cell based on values in different cell
I have 2 Columns: Value Date and Trade Date , there are multiple rows which will have value
I need a logic where, if Trade Date =...
-3
votes
1
answer
63
views
Get list of unique words without numbers and symbols from paragraph range in Google Sheet?
I have list of comments in Google sheet which is sourced from Google form. It is difficult for me to list down each unique words from lots of paragraph.
I have already tested this function which ...
-2
votes
1
answer
63
views
I want to match the city with the maximum percentage using googlesheet
Okay so I have this full table, with column D as city and column K as percentage. Now, city has many rows, which is why i only want the city with the highest percentage.
I tried using Query:
=QUERY(...
- 1
0
votes
1
answer
56
views
Use Google finance to check when a stock hits a target price in a given period
I am trying to do stock backtesting in Google sheets.
I have entered scrip symbol in A3, entry date in B3, the last date to exit the stock in C3 and the target price in D3. Now I want to find on which ...
1
vote
4
answers
76
views
How to use date with LIKE inside query?
I've got a google form(which feeds to Google sheets) for users to enter information. One of the fields is a datepicker which has this format mm/dd/yyyy.
I have a workbook with several sheets. One ...
- 43
2
votes
1
answer
131
views
Seeking formula to continue multiplying until a specified addition number is met
Using the data from the chart (see table below), I need to create a new chart to continue adding up line-by-line totals divided by subtotals until 40 hours' worth of dollars is added up. This is ...
- 21
0
votes
2
answers
79
views
Google Sheets REGEXREPLACE - exclude matches based on last character
Source text:
Basic medication kit of dentistry Drugs box containing Salbutamol; Glycerol-Trinitrate; Glucagon and Adrenaline
Formula:
=TRIM(REGEXREPLACE(G20,"([A-Z][\w\-]*[^;])",CHAR(10)&...
- 715
0
votes
2
answers
55
views
Google Sheets - returning Nth result with multiple matching criteria
I have a sheet of customer details (names, customer weights, booking date) and I need to generate a day manifest for specific dates. May be easier in another application, but need to be accessible to ...
- 3
-1
votes
1
answer
48
views
IF Status Formula Google Sheet Approval
I need to know the status of my ticket approval..
=IF(A2="","",IF(B2<>"","FOR 2ND APPROVAL",IF(C2<>"","FOR 3RD APPROVAL",IF(D2&...
2
votes
2
answers
60
views
Finding which column of a Google Sheet a value is in
I'm looking to see if there is a function which can help here on Google Sheets. I have one sheet with a list of 'names' (ID numbers in the actual data but generated a sample with names) and a second ...
-4
votes
1
answer
128
views
How do I get Free Cash Flow data from Finviz on a Google Sheet?
I'm scraping data from Finviz for financial information such as P/E ratio, Debt/Equity ratio, etc., and I can't get free cash flow growth. My formula on google sheets for Currant Ratio for McDonald's, ...
- 121