Questions tagged [google-sheets]

For questions about the Google's web application to create and edit spreadsheets on a web browser. 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 chances to get a satisfactory answer.

Filter by
Sorted by
Tagged with
0
votes
1answer
33 views

VLookup Not working

I am using Google Sheets and have a VLookup set to find the row in a separate sheet (tab) based on a cell and return a link in the fourth column. I am using this formula: =IF((ISBLANK(C14)); ; ...
0
votes
0answers
26 views

How to record a user's e-mail when they click a button in Google sheets?

I am new to Google sheets and scripting. I've seen samples of scripts here that allows recording of the current user's email when they make an amendment in a sheet. Is there a way for me to click a ...
1
vote
1answer
50 views

Check for regexmatch in multiple ranges in Google Sheets

I have a sheet that auto-populates with a sub-set of articles (each a row with a column representing various information such as title, year of publication, author name, etc...) on the basis of a set ...
1
vote
0answers
12 views

Creating auto-updating indexed links to tab in spreadsheet

This spreadsheet is a lesson planner for teachers. It will be used by a wide variety of teachers with varying tech skills so it needs to be straightforward and not easily messed up. The first sheet ...
1
vote
0answers
46 views

Conditional Formatting IF/AND Google Sheets

EDIT 2: Test document. See comment of what I'm trying to accomplish in cell A1. https://docs.google.com/spreadsheets/d/1lqrPbVqSrO257PqOYKLuCTxG1iiLaL6KYawp1hu4n5c/edit#gid=0 EDIT: I've tried this ...
1
vote
1answer
22 views

How to convert strings separated by comma from base 36 to decimal

I'm getting input from a google form. An associate scans a QR code giving a string like ^12t34:1 Cell B1 = ^12t34:1 The string between the ^ and : is base36. I can use C1 =DECIMAL(SUBSTITUTE(...
-1
votes
1answer
24 views

How to categorize/tag cells containing long text according to a list of phrases?

I have cells in a google sheet which have long abstracts. I need to mark/categorize these abstracts in an adjacent column, according to a table of phrases and corresponding tags in another sheet. As ...
0
votes
1answer
14 views

Google Sheets countif range with date/timestamp

I have a lot of requests coming in with the following timestamp (Created time Column) and I would like to quickly be able to count how many requests came in during each hour. I found I can get a code ...
0
votes
0answers
34 views

Google Sheets Form Response Split Multiple Responses

I've created a form for my staff to enter metrics about their job so I can track how new operations affect their output. For the ease of my staff, I'm hoping that they can submit multiple responses ...
0
votes
0answers
5 views

How can I create conditional formatting based on a reference table?

I'm trying to format rows in my spreadsheet based on a single column (S), which is fine BUT S contains both the category and the status of the item in each row. I want to color the row based on the ...
0
votes
1answer
11 views

How to compare values in two columns?

If I have the following two columns with values: A B 2 3 4 1 7 10 8 1 9 5 How do I create a count for column B that shows values greater than the same row in column A? In this ...
2
votes
1answer
28 views

Pasting text without interpreting as formula

I'm trying to copy a table of data from a website into Google Sheets. The table includes a column whose numbers are preceded by +/-, e.g., +/- 33. Excel works just fine, allowing me to paste in the ...
0
votes
2answers
155 views

How to wait for Google Spreadsheet calculations to update before retrieving cell data in Apps Script web app?

I am creating a Google Apps Script web app that uses Google Sheets as its backend. This web app basically allows users to track their work time (clock in for the day, clock out for lunch, clock in ...
0
votes
1answer
15 views

Checkbox that can display a checkmark or an X

Trying to make a checkbox that with one click it displays a check and with two clicks it will display an "x". is this possible? I've been looking for a way but the only thing I can find is a drop-...
2
votes
0answers
28 views

Google Data Studio and Slack integration

How Can I connect my Google Data Studio reports, or parts of them with slack? I would like that each day appear in slack some report that I will customize in Google Data Studio. And if I want to do ...
0
votes
1answer
20 views

How can I extract specific information from the cells?

How can I extract just the: Brand, Showroom Only Product, etc.
0
votes
1answer
11 views

Custom side menu question & active sheet

I am trying to create a custom side menu that changes depending on what sheet I am on so the side menu is relevant to what I am on. I've attempted doing some code to do a tactic where depending on ...
0
votes
1answer
24 views

Having trouble outputting GOOGLEFINANCE data

My wish is to create a table in Google sheets looking something like this: price day A B C D ... Z 1997-01-02 132.54 . . . . 1997-01-...
0
votes
1answer
17 views

Google Sheets Scatter chart: multiple data points at same X location

I have a dataset where I have on each row data associated with an item that has a name (alpha, beta, gamma in the example), as follows: I now want to create a scatter plot where the X axis ...
0
votes
2answers
29 views

Is it possible to have Google Sheets query function that accepts a dynamic query argument instead of hard coding?

I have a long OR query function that I will be reusing many times in many contexts and that may change frequently. Following the principles of DRY design, I would like to write this query once and ...
1
vote
1answer
13 views

Total Spent Formula in Google Sheets

So with my sheet, I am currently in the process of adding more stats in, I have looked into adding these stats by using Index & Match however it will only match to the first correct match it has ...
0
votes
1answer
14 views

Google Sheet Calculation Only Updates on Return

I am using the formula below. It will only update when I go into the cell and push "enter" to leave the cell. I think it is due to the iferror function. It is calculating correcting but I have to hit ...
1
vote
1answer
48 views

Get last value entered past a specific row

I have been on this a couple of days and I cannot get my head around this ... I use a spreadsheet to track the number of miles traveled by drivers and I want to work out how many miles a driver has ...
1
vote
1answer
12 views

Conditional Output on cell based on 2 different “IF(REGEXMATCH” functions

First of all sorry if I am not clear. it is my first question. I am trying to make a cell give an output with IF(REGEXMATCH on the cell A1 for example =IF(RegExMatch(A2,"YES"),"Good" I also need ...
0
votes
1answer
28 views

Have script ignore first row [duplicate]

I am using this script to add in a date whenever a row is edited. I want it to ignore the first row. Thoughts on how to do this? function onEdit() { var s = SpreadsheetApp.getActiveSheet(); // ...
0
votes
1answer
33 views

If cell y contains any word from range x:x, mark cell y green

In Google Sheets, I have three columns (with different lengths!) Column1 contains firm names (50) Column2 containts firm names (100) I would like to mark a cell in Column1 green (or with a number in ...
1
vote
2answers
35 views

Formula for getting the Left most cell value from a 'Range of cells'

Need your support in finding a solution for an issue which I am facing now. Formula for Column A... Data Range from B to D Have values in a range of spreadsheet cells (From column B to D) Value ...
1
vote
1answer
12 views

Google Sheets forumla help

With my sheet, I need it so that if the job is any type of "Repair" then it minuses 1000 from the business profit but if it's anything else it doesn't minus the 1000 from the business profit. I have ...
0
votes
1answer
25 views

How to merge data from multiple Google Sheets with formula?

I have 10 columns in 10 Google sheets and in all sheets are the data labeled like Month | Total number of winter/summer schools attended | Sr. No. | Title | Date | Venue | Organisation | Number of ...
0
votes
1answer
10 views

Using UNIQUE and SORT together

I'm currently using UNIQUE to look through a spreadsheet and display in another spreadsheet all the unique names in a certain "Name" column. However, I need to sort these names by another column ...
0
votes
1answer
7 views

Is it possible to use a Google Sheets formula to control the value inside of an inserted text box (drawing)?

For aesthetic/design reasons I would like to be able to have the value of a COUNTA calculation be shown as inserted text (ie outside of a cell and can be arbitrarily dragged and dropped). Is this ...
0
votes
0answers
6 views

How to auto populate potential email combinations in Google Sheets

For example I have an email for John Smith and am trying to find out his appropriate middle initial. The email format is smith_john_(middle initial)@company.com but I cannot get the email to auto ...
0
votes
0answers
12 views

How to retain the current style of google sheet to a newly added row from a Google Form

I have setup a google form for a daily task list. And we keep adding new information every data. I have setup a theme and styled the Google Sheet the way I want but when someone adds a new entry from ...
0
votes
1answer
29 views

Calculate hours worked but not limited to a single day

I'm trying to automate getting the number of hours worked for a session, not a day. My raw data is the following: Here is a sample spreadsheet for the data I was able to create a formatted version ...
0
votes
0answers
11 views

Connect a single cell of a Google Sheet to a Google doc

To get data from a google sheet into a google doc where the latter updates when the sheet is changed I can simply select more than one cell, use edit-copy in the sheet and then edit-paste- and choose "...
1
vote
1answer
31 views

How to Update a exactly sheet tab row with a value from a imported data in another tab in Google Sheets?

I have this tab in Google spreadsheet [https://docs.google.com/spreadsheets/d/14ZBff8R6brLYWtfv70V-tyG0HE-sX_ejySvplQqZftM/edit#gid=1450402909] Sheet All Tasks, Candice Tasks, Others People Tasks ...
1
vote
1answer
14 views

Conditional Formatting in Google Sheets not working correctly

I recently tried to edit the conditional formatting for a column that was working correctly before and then change it back to the same exact formula it had before and now it isn't working correctly. ...
1
vote
1answer
26 views

Returning max value based on two conditions

I've been reading through a lot of other solutions to similar problems but am not able to resolve my issues. I have a Google Sheet with two sheets I am working with: Sheet 1 A list of contacts (...
1
vote
1answer
13 views

Rank days of the month that occur most

I would like to know which days of the month customers have historically paid on the most. I would like to create a report that has days 1,2,3,4...31 all with a count next to each day for how many ...
2
votes
1answer
17 views

ArrayFormula to create a table of data from 1 column

I have the below formula and want to turn it into an array. =INDEX($A:$A,ROW(A1)*5-5+COLUMN(A1)) I have tried it with the below attempt but only get 1 single cell result. =arrayformula(INDEX($A:$A,...
0
votes
1answer
39 views

How to detect duplicates across multiple sheets?

I have found a couple of formulas/scripts for what I am looking for, but they are all locked for one sheet only, thus I will try to explain what exactly I need. I am unable to do it my self, because ...
0
votes
1answer
16 views

How to automatically add a row above a particular cell?

I'm sending some data from Zapier to Google Sheets and every time Zapier creates a new row, all formulas from that row are gone. I'm looking for a script that would allow the following solution: I ...
0
votes
2answers
14 views

Query twice in the same cell to receive a name next to a number in a row

Sheet link I posted before however, I don't think I was specific enough. I am sorry if I am wasting the time of anyone. In my sheet, you will see three pages. On the "Search Notes" page in C5 I ...
0
votes
1answer
42 views

Unable to change Google Sheets data point format without changing other data points

I am trying to ensure that each bar in a horizontal bar chart in Google Sheets has its own unique color. Conventional wisdom seems to entail either select the chart --> left-click on the bar -->...
2
votes
1answer
18 views

Google Sheets Import Range Query Error

I'm trying to create a sheet that only shows me LOT Codes of specific products from a different workbook that has all of our products and LOT Codes. From what I've seen the Query with Import Range ...
0
votes
1answer
13 views

Google sheet formula for previous 7 day high

Need a formula for previous 7 working day high for a share =INDEX(GOOGLEFINANCE("nse:"&A2, "high", TODAY()-3.3), 2, 2) Used the above formula but I need only workday formula.
1
vote
1answer
11 views

Highlight data that match with data from other sheet

I have two tabs in a Spreadsheet. Both are having a list of data. I want to highlight cells in one tab that match with cells from another tab. For example: Tab 1 Column 1 ABC XYZ PQR DEF Tab 2 ...
0
votes
1answer
29 views

Mark Google Sheets comment as done via email?

Is it possible to mark a Google Sheet comment as done from that email you're receiving from <comments-noreply@docs.google.com> if someone adds a new comment for you?
1
vote
1answer
28 views

Is there any way to dynamically modify dates based on number of days something would take?

Imagine you're creating a Google Sheet to track all of your tasks, how long they take, and their deadlines: Task |Number of days| Start Date | Date Done Buy plane tickets | ...
0
votes
0answers
14 views

Podio and Zapier integrations

I am using Podio and Zapier to do some integrations to Google Sheets. Here are the things I wanted to hopefully happen: When a Podio app item is triggered (by a field change), It should update the ...