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 a demo spreadsheet to your question; this will greatly increase the chance that you get a satisfactory answer.

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

How do I use a query to show a row with a name and also show the next 8 rows below it on Google Sheets?

I am using google sheets to have a group of people fill out when they are available and what it to show up on another spreadsheet and be able to move as well. 0 signifies they are free and 1 means ...
0
votes
0answers
8 views

Results of formulas in same cell

I have a weekly tracking sheet which I'm trying to get the result of 2 averages next to each other in the same cell. Column B has a number value in each cell for each day of a week Column G has a ...
22
votes
7answers
70k views

Keyboard shortcut to merge cells in Google Sheets

Is there a keyboard shortcut to merge cells in Google Sheets? I've looked and can't find it. F4 doesn't repeat merging cells either. I'm on OSX, Apple keyboard. Anyone been able to do this with a ...
0
votes
1answer
174 views

Conditional Formatting in Google Sheets based on the same rows conditional format

I have a spreadsheet where I have tasks, and I prioritize those tasks based on high, medium, and low levels. I currently use conditional formatting for this, so if I select from a drop-down "HIGH", ...
0
votes
1answer
7 views

How to query data range including data formatting (color, font) in Google sheets? [duplicate]

How to modify this formula to make the result get the data with its original color & font formatting. =query(Sheet1!A1:D,"select A,B,C,D where A is not null",0) Lets say I want only ...
0
votes
0answers
9 views

How do I calculate totals of certain words used in a workbook?

https://docs.google.com/spreadsheets/d/1U2Sm6DyqIB93As7282xEWDBG_Rre6IFhCKl6th-krb0/edit?usp=drivesdk I'm trying to find out what products are the most commonly used on r/skincareaddiction. So I want ...
5
votes
2answers
6k views

Issue with IMAGE() function

I've an image in my Drive URL = "https://drive.google.com/open?id=0B7Qr0kRr6yOOLXB1OE5lUUxBRjQ" I converted it to "https://drive.google.com/uc?export=download&id=0B7Qr0kRr6yOOLXB1OE5lUUxBRjQ" ...
0
votes
1answer
39 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 ...
0
votes
2answers
13 views

How to get two separate links in one Google Sheet cell

I'm trying to figure out how to display two separate words, each with its own unique hyperlink, into a single cell in Google Sheets. Example is in the image below. Can someone please help me figure ...
0
votes
0answers
5 views

Reset/Clean Up Data Based On Fixed Date/Time In Sheets

I searched online but there's no solution to this one I could find. as per the sheet attached, there are two tabs: DAILY POSTS and SUBMIT. cells market in yellow colour are where a user would select/...
0
votes
1answer
10 views

Google Sheets SUMIFS - apply REGEXTRACT to sum_range?

I have the following Google Sheets table and want to calculate the total sums of various medications prescribed: A B C 1 Type Amount (in g) Daily dosage (in g) 2 A 60 2 3 B 25 mg/capsule 30 pieces,...
0
votes
1answer
30 views

Google Sheets, multiple sheets, different rows

First, I apologize if my use of quotations and such are not correct. I will try my best to explain my situation and I appreciate any help provided. I have one Workbook with multiple sheets. Sheets are ...
0
votes
0answers
12 views

How to randomly pick a cell out of a range and print its value in a different cell in Google Sheets?

I have made a range of cells. Each cell has a specific text in it. I want to first pick a column randomly and then pick 5 cells from that column randomly and print those 5 cells in another cell. Is ...
0
votes
1answer
13 views

How can I use a Drop Down List and skip data validation in Google Sheets?

I want to show a Drop Down List in a cell in Google Sheets, where user can choose a list of words. But I want to let to user to input any number too. Since the only way I know to show off a drop down ...
1
vote
1answer
664 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
1answer
14 views

Google Sheets sorting and lookup

I have a table like this : Now I would like to perform a column lookup on this where I give the Client(B) and get return value from Status(C) but the latest date from Date (A). I tried using vlookup ...
0
votes
1answer
10 views

Is there a way to change “Tab Key” Function in Google Sheets to that of “Enter/Return Key”. Like a way to change the Key Bindings Temporarily

If you Select two columns, and keep pressing tab key, it starts the selection of the cell from A1, Then moves to B1 & then to A2, Then to B2 and so on. I need to be able to use Enter key to ...
0
votes
1answer
11 views

Can I quickly sort Google Sheet via buttons?

I've got a Google Sheets document with a lot of data that I often need to sort. Google Sheets offers a sorting feature but I'd personally like button on the header that allows sorting of the rows ...
0
votes
1answer
15 views

Google Spreadsheet Alert Scripts

Wanting to add an alert so if the information entered here if not all filled in as required it send you an alert. function Repair1() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var r ...
0
votes
1answer
10 views

How to get Google Sheets to show a sort by column UX?

NOTE: This question is NOT about how to sort rows by selecting them and then choosing Data->Sort Range Rather, I want to know if it's possible to add or enable a UX here there's a sort button on ...
0
votes
1answer
3k views

Show/Hide Rows based on text in a cell

I'm trying to toggle which rows are shown/hidden on multiple sheets in my Google Sheets document. Simply put, a user will provide an input on Sheet1, which will populate a "show" or "hide" value in ...
1
vote
3answers
103 views

Google Sheets Checkbox vs. Tickbox

Sometimes, I've seen Checkbox but I've also seen Tick Box. Does anyone know what's going on?
0
votes
0answers
10 views

How can I extract the directed acyclic graph of a google sheet? [closed]

This may belong in stackoverflow, but since I'm hoping that the answer is some plugin, I'm asking here first. Google Sheets doesn't have the "show dependencies" that excel does. I'm trying ...
0
votes
1answer
10 views

Return different value for condition in ARRAYFORMULA

I have an ARRAYFORMULA that combines text with a number. A B 1 textA 0 2 textB 1 3 textC 2 =ARRAYFORMULA(A1:A3&" "&B1:B3) But if the value in the B column is 0, I want the ...
0
votes
0answers
15 views

Is there a way to import a google docs table into a google sheet?

Working with a newspaper, and they want a table on the top of documents to indicate some things about the status of the article. I know google docs natively supports importing a sheet into a table, ...
0
votes
1answer
24 views

Google Sheet Search Button

Sorry beginner and just starting to try Apps Script just for a few weeks. I'm create a form for my Inventory Tracker with few button 'Search', 'Clear Form', 'Save Data', 'Update Data' and 'Delete Data'...
0
votes
0answers
7 views

How to use use cell references in the WHERE part of a QUERY / IMPORTRANGE formula with numbers and / or letters [duplicate]

Here is my formula on a test page accessible via this link: https://docs.google.com/spreadsheets/d/104EFtLEv4Ua0UYn0vVoK3wkFpOFuGad401zK6X0elAQ/edit#gid=0 F4 = 22/12/2020 Data imported with ...
0
votes
1answer
28 views

SPX 500 Monthly Return Google Finance

I need to formulate the SPX500 return from "x start date" to "x end date". I have managed to figure out "x start date" to current date (year to date return) but I cant ...
2
votes
2answers
2k views

Set calculation precision in Google Sheets

Google Sheets uses some sort of floating point arithmetic in its internal calculation, even if only integer numbers are in the calculation. For example: A1: =128^128 A2: =A1+1 A3: =A2-A1 Then, A3 ...
0
votes
1answer
5 views

To fetch stock price day's high and low for a range of days including today

For shares listed on NSE i am unable to get Day's high and low while using below query if the start day and end date is same or today. =MAX(QUERY(GOOGLEFINANCE("NSE:INFY", "ALL",06/...
0
votes
0answers
16 views

Google Sheets - Highlight nearest value - Swimming Times

I'm banging my head against a wall trying to find a way to highlight a swimming time compared to results from past swim meets. Column A is my swimmers time Column B is the event Column D is the time ...
0
votes
1answer
25 views

Is there a way to add a new sheet tab to a Google Sheet based on the timestamp date?

Students take a weekly survey on Google Forms, and it is transferred to a Google Sheet. I want the Google Sheet to automatically generate a new tab based on the date (timestamp) so that each week has ...
0
votes
1answer
12 views

Next 5 working dates

I’m trying to find a way in Google Sheets to list the dates for the next 5 working days. I’ve got it to show me the next working week, however as today is a Wednesday ideally I would like the date for ...
0
votes
1answer
16 views

Filtering views based on a cell having a value

I am trying to sort out a spreadsheet which has multiple columns in but don't really know where to start! I am looking to filter the sheet where column C has more than 3 errors for the ID on column B. ...
0
votes
1answer
10 views

Moving average up until every listed week

I'm trying to automatically calculate a new average every week, so I can see the evolution of the average over the past weeks. I have one column with the week numbers. The second column contains an ...
1
vote
1answer
1k views

Reference entire row data or row-by-value from other sheets

I've a Google Spreadsheet where my first sheet is a Products table. The columns are SKU, Title, Price, Category, Type I've some tasks in front of me where I need to create some bundles of different ...
0
votes
1answer
21 views

Finding the longest time duration in Google Sheets

I'm trying to find the longest time value (hh:mm:ss) and I'm looking for it in column I, starting at row 5. I've already tried using the LARGE function but that outputs a decimal, not a time. =LARGE(...
0
votes
1answer
16 views

Determining type of hours worked past certain time of day

Link to sheet: https://docs.google.com/spreadsheets/d/1dwpeRWkkbX0adBeQkwMUI6PQa6WqRCNzNluSOw0RU5k/edit?usp=sharing Our business pays a different rate for drive time vs. site time. We also pay ...
7
votes
3answers
27k views

Unable to parse query string for function Query, when using IMPORTRANGE

This is the problem I'm seeing: Error - Unable to parse query string for function QUERY parameter 2: NO_COLUMND The formula is of the form: =query(importange("...", "Sheet!A:V"), "select D where ...
0
votes
2answers
37 views

Use array as function parameters

Given a function that returns an array, is it possible to "spread" the return values as the arguments to another function? For example, I'd like to pass the result of SPLIT("example.com,...
0
votes
1answer
30 views

Indirect formula to add table field to vlookup formula

So I have to get a price based 3 variables Diamond size Diamond quality Diamond Shape So I made tables depending on shapes This formula is working Diamond size = H11 = 1 Diamond quality = $K$3 =...
0
votes
2answers
22 views

How to pick a random row that also does not contain text in one cell?

so I have a google spreadsheet full of youtube videos. Column A looks if the video is already in column B, and if it is it says "duplicate" Column B is youtube link Column C is the title of ...
0
votes
1answer
14 views

Sync dropdown cells and edit it

Sorry english is not my native language but I can't find anything to this topic and I hope you understand my writing/problem and can help me. I have a huge google sheet with many columns. But for this ...
2
votes
5answers
4k views

Search in hidden rows in Google Sheet

When I search the current sheet using Ctrl+F in a Google Sheet, hidden rows aren't searched. I know I can manually unhide all the hidden rows and then do the search, but then I would have to remember ...
0
votes
1answer
1k 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
votes
0answers
11 views

¿Como hacer que la formula siguiente: =CONTAR.SI($D$2:D3,D3), se ejecute con ArrayFormula? [closed]

La formula me da en cada fila el numero de veces que se va repitiendo el criterio, pero no he logrado ponerla en un ArrayFormula, para no tener que estar copiando la formula cada que se inserta una ...
0
votes
1answer
16 views

Maintaining absolute references when adding new rows to a sheet?

When I add a new row for the current reporting month to the top (becomes row 9), my absolute cell references in my formulas on rows 3–5 change to move down a row, now referencing rows 10 and 11, ...
0
votes
1answer
12 views

How do I code 3 series dependent drop down on multiple sheets?

I have a code to run a 3 tier dependent drop down list - it currently works for one sheet in my workbook named "BlankNode" but I need it to work for ~20 different sheets. I tried adding a ...
0
votes
0answers
19 views

“Count” from unspecified starting points in google sheets

I'm a baseball coach creating a google sheet to help track pitches and results for my college guys during games so they can see a lot of different information. See attached picture. I'd like the chart ...
1
vote
1answer
27 views

In google sheets - how return *1.54M* as $1,540,000 with the same formula that I would return $1.35B* as $1,350,000,000?

I am building a stock model - where I use =importhtml() to give me financial data pulled from a website on certain stocks. It is returning it correctly, but there are asterisks surrounding all the ...

1
2 3 4 5
138