Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [formulas]

Formulas used in spreadsheets edited by web applications like Excel Online, Google Sheets, among others.

0
votes
1answer
4 views

How to select existing sheet to be populated by a Google form?

I have a team working in pan India with 6 Team leaders. I have created a Google form to collect the everyday status report. Now the question is: How can I assign a particular sheet[already created in ...
0
votes
1answer
6 views

How to use a cell value to define the sheet on IMPORTRANGE

Trying to create a spreadsheet that reads values from a certain sheet of another file using importrange formula. I am trying that the referenced external cell be a cell on the current one so I could ...
0
votes
1answer
7 views

How to import historical open price in Google Sheets with GOOGLEFINANCE?

For some reason, I can't get the google finance function to work with any open-price lookups I do. For example: =GOOGLEFINANCE("AAPL","priceopen", DATE(2019,4,12)) comes back as #N/A with message: ...
0
votes
1answer
13 views

Simple Scorecard Conditional Formatting

I simply want to create a scoresheet comparing scores to par. If less than par then a red number, if more than par then highlight red, if equal to par then highlight green. Here is what I'm trying ...
0
votes
1answer
15 views

Using a cell value, to dynamically create a formula, that links to a cell in a different sheet (tab)

Ok. This is a bit complicated, but I will do my best to describe the problem. All of these elements are located in multiple Sheet pages (Tabs) within one singular Google Sheet (Workbook). Sheet 1: ...
1
vote
2answers
29 views

How to keep a checkbox value to reuse it later in Google Sheets

I'm working on a Google sheet actually (only for fun) and I'm actually stuck on something, as I said in the title, I want to keep a checkbox value in Google sheet. I will explain my problem. I'm ...
0
votes
1answer
19 views

Grabbing only non-empty cells from a row range for every row

I have a survey that sends responses into a Google sheet. The raw data that is inserted is messy, so I have another sheet that processes the incoming raw data. One of the things I need to do is: from ...
1
vote
1answer
12 views

Compile cells when cell contains certain value

In Google Sheets, I have a range of values as follows: I am looking for a formula that will search the range, find all of the cells that is a 'wire' (contains the term "wire") and outputs the range ...
0
votes
2answers
13 views

Format cell if all the cells in a row are not empty

I tried all kind of formula but they all seem to work the other way around for me. I have a very large sheet with lots of columns. I'd like to have the first cell in the row to be a certain color if ...
1
vote
1answer
20 views

How to convert multi-value answers into single column in Google Forms' response sheet?

I have a Google form that asks for Name and up to 3 Score. The data is dumped into a sheet like this But that data is not very convenient. I want to have the data like this... Can I do that by ...
1
vote
1answer
11 views

Finding multiple “most common values” in a column

I am trying to display the 5 most commonly appearing text values in a column in Google Sheets. The following will give me the most common value in column A: =ArrayFormula(INDEX(A:A,MODE(MATCH(A:A,A:...
0
votes
1answer
9 views

Copy Google form and linked sheet preserving existing formulas and formatting?

I am required to copy an existing Google Form, however, the linked results sheet has deep formatting and formula. Apparently, the new form requires a new sheet to be linked to form results. How can ...
1
vote
1answer
11 views

Formating a number with specific spacing

I'm attempting to pull a particular option value from Yahoo! into Google Sheets. The base URL is: https://finance.yahoo.com/quote/GOOG190418C00800000 When breaking the formula up, I have to ...
0
votes
1answer
9 views

Description based on each checkbox “checked” to populate into a summary on the 2nd tab (sheet)

I am building a spreadsheet that has to tab (sheets), the 1st I have an "assessment checklist" with about 250-30 checkboxes based on what is found. Example (Checkbox is in "A" column and description ...
2
votes
1answer
10 views

Format cell, If word from list in cell

I'm trying to color tag cells according to keywords they may contain. I've searched just about everywhere, and have tried various applications of the Search and Find functions to no avail. Imagine I ...
1
vote
1answer
13 views

Disable Conditional Formatting without deleting the rules

I have multiple conditional formatting rules in one of my Google Sheets, I would like to disable a few of those rules for some time and would like to enable them again. But I don't see any such ...
0
votes
1answer
18 views

How to navigate to a certain sheet in Google Sheets?

In my previous related question, I asked how to navigate to a certain column, however, now I also want to navigate to a certain sheet. So I need a link from a cell, and by clicking it, I want to jump ...
1
vote
2answers
19 views

How can I create a recursive formula in Google Sheets?

I'm making a task tracker in Google Sheets and am trying to implement subtasks. I am trying to create a formula that checks a row's "Parent Task" column, and if there is a parent task, indents the ...
0
votes
2answers
14 views

How Do I Quickly Merge A Large Amount Of Cells?

So I'm trying to merge all cells in Col A and B so that A1 and B1 are one cell all the way for 150 rows, anybody knows how?
0
votes
1answer
25 views

I want to get rid of google finance disclaimer

I want to get rid of Google finance disclaimer. I used the =GOOGLEFINANCE formula in a sheet. It triggered the display of a disclaimer at the bottom of the sheet that says: Quotes are not sourced ...
1
vote
1answer
18 views

Total Wages Calculations

https://docs.google.com/spreadsheets/d/13HdNN_qa9rhLFyMe0N9I9EusmBudi_dcdBkc6UeLHMM/edit#gid=878850570 I am trying to total Normal Pay but the formula does not Multiply Regular Hours by Normal Rate ...
0
votes
1answer
23 views

Find previous non-blank cell, but without a specified range in Google Sheets

I've come across plenty of formulas to find blank cells within a certain specified range, but can't seem to figure this out / find an answer. I have a spreadsheet kind of like this: . Where column ...
0
votes
1answer
10 views

How can I conditionally format cells to denote past/future/current dates?

I have a church rota Google Sheet. Column A shows the date of each service. To make it more obvious at a glance, I would like cells in column A to apply formatting so that dates in the past are ...
1
vote
1answer
18 views

Problem inserting a column and cell reference/link

I have a sort of summary in my Google sheet. In the second column, I refer to the sub tables further in the sheet with e.g. this content: https://docs.google.com/spreadsheets/d/1J- ...
0
votes
1answer
7 views

How do I insert different cell data from a table into a hyperlink?

I have a table of different cell data that I want to insert into different hyperlinks for each item: =HYPERLINK("https://www.otcmarkets.com/stock/CELL DATA TO BE INSERTED/security","SECURITY") Cell ...
0
votes
1answer
7 views

How to ignore certain strings when creating unique list of words in Google Sheets?

In Google Sheets, I am using the formula to create a unique list of words: =unique(transpose(split(ArrayFormula(concatenate(B2:E"+")),"+"))) But I want to filter out certain strings. My unique list ...
1
vote
1answer
7 views

Automatic cell coloring via conditional formatting

I am working on a sheet for a game I play and having some trouble with cell coloring. I have one sheet, named "PlayerList" which maintains a list of player names, class and role. Players are ...
0
votes
2answers
7 views

How to create a unique list from a selected range of cells

I have a list in cells A1:A16 of foods and want to create a unique list with each item only appearing once in the list: Eggs Bacon Hashbrowns Baked Beans Avocado Coffee Bread Bread Sandwhich meet ...
0
votes
1answer
18 views

Method to preserve cell reference in a formula upon adding rows in reference sheet?

I have a Google Sheets with two(2) tabs. The first sheet is just a running tally of raw data and is updated with new rows at the top weekly. In tab 2 there is just a single query(arrayformula)... ...
1
vote
1answer
8 views

Google Sheets referencing a cell adjacent to a cell on another sheet

I've got a calendar in google sheets with each cell referencing a cell value pulled from weekly sheets. I'd like to be able to enter the cell reference for the first of the month, then have the rest ...
1
vote
1answer
16 views

Selecting row with best value for items with several versions/upgrades

I'm sure my problem is quite simple to solve, but I've been on it for a while and I could use some help. I have a huge table, but the problem boils down to this: some items have 2 versions (one ...
1
vote
1answer
11 views

Autopopulate a RANGE of Cells from one sheet (tab) to another in a single workbook

All I want to do is have cells in one sheet (tab) in a workbook auto-populate with data and formatting of a different sheet (tab) in the same workbook. I know how to do this one cell at a time, BUT is ...
0
votes
2answers
12 views

How can I group by one column to join values in multiple rows?

Let's say I have a survey of people in teams, and what fruits they like: User | Team | Fruits ---------------------- Dave | Mine | Banana, Apple Steve | Mine | Apple, Orange Matt | Yours | ...
0
votes
5answers
26 views

Layperson's grid to normalized table

Introduction  (too long, don't read) I have a data-arrangement problem. I'm now up to three qualifying files from coworkers, and for that matter, I've run into a couple of questions here on SE ...
0
votes
1answer
27 views

Reference all tabs with a specific word in COUNTIF statement

Basically, I am trying to make a master tab that adds up all of the data from all the other tabs. These tabs are weekly breakdown numbers for ad statistic numbers, so there is a new tab added every ...
1
vote
1answer
8 views

Finding the number of instructors available on different weekends in Google Sheets

I'd like to find a generic way to see when resources are available, in order to help plan when activities can be run. In this case, the resources are people with certain instructor qualifications (...
0
votes
1answer
18 views

Divide data evenly in row, place in additional rows

I am using Goodle Sheets, and I have a simple problem. I am looking to divide the data by 4 in many rows, and place the results in new separate rows, replacing the original row. The new rows should ...
1
vote
2answers
19 views

Google Sheets monthly rolling average

I have a table in Google Sheets with data for different people by month. I want to display a monthly average that will be progressive as the year goes by. So they can have an idea of how they are ...
0
votes
1answer
7 views

Odd result in Google Sheets calculation

I am editing an existing spreadsheet in Google Drive and the formula below is giving me an answer I cannot figure out. =if(isBlank({H6}),ifError(1/0),(( ArrayFormula( ROUND((P6*1.2)))-0.05))) where ...
0
votes
1answer
7 views

How do I copy over a value from another worksheet depending on the adjacent column?

I have two checklists of the same items in a different order in two separate sheets, and I want to only update one. Basically, I have two pages that look like this And I want the? to be automatically ...
0
votes
1answer
10 views

How to TEXTJOIN in Google Sheets

I have ten columns in my rows which contain 5 sets of first name/last name. I want to combine each of these names in a final column using TEXTJOIN (or whatever function is appropriate if I'm missing ...
1
vote
1answer
15 views

How do I get rid of the blank line when I use UNIQUE in Google Sheets?

I used unique... the result is in column B, but I want results to look like column C. So what do I need to do to achieve this?
1
vote
3answers
44 views

Filter cell data using Query/Importdata

I am attempting to grab data from a cell, by using additional data in the cell above it to make sure its correct. Here is the data, each line is put into a cell by the formula. <total_count>6&...
0
votes
1answer
13 views

Replace value in field automatically (auto-correct?)

I'd like to replace occurrences found in Column A (cells A2:A200) so that if I enter 2/02/002 or 343 or 98/098 it auto-replaces it with UKD002/UKD343/UKD098. Is this possible?
0
votes
1answer
14 views

Collates the number of depots that don't have manifests and trunk headers

Trying to do a work project that collates the number of depots that don't have manifests and trunk headers. Column A: Depot Numbers Column B: No Manifest record (i.e if Colum B 2 - 200 contains "...
0
votes
1answer
10 views

Data validation if no then disable cell else drop-down lists

I'm trying to do a drop-down list IF another cell doesn't equal "no" So I have two columns: one with a drop-down list containing "yes" and "no" and in the other column I want to do a drop-down list ...
0
votes
2answers
9 views

Update each cell in column from upper right adjacent cell

How can I make the start mileage in each cell update from the end mileage in the upper right adjacent cell? Preferably using something like a single array formula that can be updated. I think I'm ...
0
votes
1answer
13 views

INDEX + MATCH to return the greatest of all matching values

I'm looking for the most recent date corresponding to a particular name. Dates are not necessarily ordered. I can't sort the target, I need it to work in any order. Example: When was [Roberto]'s most ...
1
vote
2answers
10 views

How do I automatically enter sequential numbers without using the mouse in Google Sheets?

I would like to create a series of number in Google Sheets, without dragging with the mouse. When using the Shortcut Cmd+R (Fill Right) it just copies the value of the first cell. Initial State: ...
1
vote
1answer
38 views

Goggle Sheets IMPORTXML/REGEXEXTRACT out of date

Alright, so I found my basic answer from these posts, 2-3 years ago. Extract data from Steam Import total hours on Steam Steam has updated its website since then, so I've messed around with it and ...