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
0answers
5 views

Adding together two amounts from different sheets on Google Sheets

I have a Google Spreadsheet, which has two sheets within. I want to take an £ amount from Sheet 1, and a £ amount from Sheet 2, and add them together in Sheet 3. Is this possible?
0
votes
0answers
4 views

Auto-sorting in google sheets and excel by custom valued sorting

I want my data to be auto sorted by 2 columns every-time I append or change values. Also sorting is not just A to Z or Ascending/Descending but Custom valued sorting as example shown below: 1- High 2- ...
1
vote
0answers
7 views

Count number of times duplicates appear

I'm struggling with this one. Column A has a students name. Column B has the name of the exam they took. If they fail the exam they will need to take it again. I would like to know which exams are ...
0
votes
0answers
11 views

Is it possible to import data into Google Sheets that is in a <div>?

I've been using =ImportHTML() but it seems a bit limited to what it can import from websites, from what I've seen it looks like you can only import from a site that has the data in a table or a list, ...
0
votes
2answers
25 views

How to make the cells of query function results with borders that are dynamic according to rows/column numbers?

https://docs.google.com/spreadsheets/d/1VsEvt2KnYGnlN_qHjkjPohVHF5MUqfyHD7tSk0VpkqA/edit?usp=sharing I would like to make the cells that have query results in All_Patients tab with borders instead of ...
0
votes
1answer
14 views

Google Sheets - value in cell different from text

Within one cell, I would like the value of the cell to be treated differently from the text inside that cell. For example, I would like the cell to display "$148 (123 euros)", but the value ...
0
votes
2answers
21 views

How do i use the query in google sheets to return N results from each tab

How do I use the query in Google Sheets to return N results from each tab. So I have this formula: =QUERY({sheet1!A2:D;sheet2!A2:D}; "select * where Col4>=65 order by Col4 desc Limit 3") ...
1
vote
3answers
20 views

Count instances of character in a column

Is there a way to count instances of characters down a column, without creating further columns that do row-by-row counts of each character? Below is an outline of what I'm trying to do. Note that no ...
0
votes
0answers
11 views

How do I make formulas effective only starting from tomorrow in Google Sheets?

I made a Google Sheet for insulin-dependent diabetics, to calculate insulin units based on their meal content and glucose reading inputs. Each day has its own block of cells for the patient. When I ...
0
votes
0answers
7 views

Jump to cell address mentioned in other cell

A1 shows address of last cell un a column (e.g. A572 or A577). Every time address will change if adding new row (there can be blank rows in between of rows with data). I need a link in B1 to click and ...
1
vote
0answers
12 views

Extract the first three unique characters of a name to create a code

I have a column D where I store the VENDOR COMPANY NAME. I have a column B where I want to calculate a SHORTCODE unique for each vendor. This code will be the first 3 letters of the VENDOR COMPANY ...
0
votes
0answers
12 views

Google sheets: Regexextract next or previous whole word or number from cell in sheets

How do I extract the next word or number (including comma and delimiter) to a search string from a cell in sheets? And how the previous? ={$AV$14; ArrayFormula(IF(ISBLANK($AT$2:$AT),, iferror( value(...
0
votes
0answers
16 views

How do I update a single Google Sheet tab with information from two tabs on the same sheet when a form is submitted?

I have two forms that contain similar information. One asks for more in-depth information that is needed sometimes from external customers while the internal one doesn't need as much information. I ...
0
votes
1answer
33 views

is there a formula that will allow me to pull data from one google sheet tab to another based on a if contains conditional

I have a list of start dates in one tab in Google sheets. I want to pull only the start dates that reflect 2021 into a new tab, as well as pull the corresponding info for that entry from the other ...
1
vote
1answer
22 views

Is there a way to detect with Google-Apps-Script whether Google Sheets is opened from Android app or from desktop browser?

I have a small script that I have further reduced for the sake of this question. Basically it implements the onEdit function in which it may show an alert (using SpreadsheetApp.getUi().alert()) and ...
0
votes
1answer
9 views

Google sheet sum minus sum gives small number when should be 0

I'm doing accounting in google sheet so my column C and D are all numbers with 2 decimal points. But somehow when I subtract sum of debit and sum of credit, the result is a small number when it should ...
0
votes
1answer
13 views

How to convert a rational number like 1.6 to the higher integer number?

How can i convert the number 1.6 to 2, 3.8 to 4 and 1.2 to 2. Any number that has0.0 to be converted to the next number . Round will round the number to the nearest integer but I want to the higher ...
1
vote
1answer
18 views

How to solve the issue of sorting numbers in 00-00 format?

https://docs.google.com/spreadsheets/d/1VsEvt2KnYGnlN_qHjkjPohVHF5MUqfyHD7tSk0VpkqA/edit#gid=1418057437 I have this sheet that I enter all data of patients in (Data_Entry) tab and I want these data to ...
0
votes
0answers
17 views

Jump to a cell, address of which is located in another cell

A1 has a formula that shows the address of the last row with data (A958, A959). I need to make a link in A2 to the cell address of A1. (eg. If A1 has Value C456, I need to jump to Cell C456 by ...
1
vote
0answers
18 views

How do I create a list of ranked numbers in Sheets––one that will adjust if I change a number?

I have a column of numbers 1-50 in a Google Sheet, which are a ranking list for a different column of fifty items (items are ranked relative to one another, so 1 is the best in the list and 50 is the ...
1
vote
0answers
9 views

How do I specify a consistent interval for a date-range axis in a Google Sheet Chart

I have a Google Sheet with a set of deposits and withdrawls and the running balance. I have added a chart to the sheet, so I can visualize what the balance has been over time and when deposits or ...
1
vote
2answers
16 views

How to test apps script addon

I have created an addon using this guide. code.gs function onOpen() { SpreadsheetApp.getUi().createAddonMenu() .addItem('Start', 'showSidebar'); } function showSidebar() { const ui = ...
2
votes
2answers
49 views

How to sum a range columns from another google sheet

I get this daily data consumption spreadsheet report from a vendor that looks like this userid feb1 feb2 feb3 . feb29 u1 100 34 23 . 4 u2 0 24 ...
0
votes
1answer
10 views

How to match formatting of one cell in Google sheet with another?

I typed "2:30pm" into a cell in a Google sheet, and it formatted it as "2:30 PM". I typed "2:30pm" into a different cell, and it formatted it as "14:30". What I ...
0
votes
0answers
16 views

Remove duplicates but keep data based on several criterias

I have a database with thousands of rows and want to remove duplicates but also keep records from earlier in certain conditions. Here is my problem: Column A will have unique numbers for referals in ...
0
votes
1answer
20 views

How to apply a conditional formatting rule on the last row of similar cell value across one column?

I am trying to conditionally format the last row that has similar values of sorted data based on country column. In this example of my test sheet, I'm sorting the data from A3:C18 by placing the ...
2
votes
1answer
41 views

Adding together multiple Sum Filter or Sumifs with multiple conditions across multiple sheets?

So I have a budgeting spread sheet. I have an overview page that I need to link to a "Checkings" sheet and a "CC-Chase" sheet. To calculate the amount spent on a specific category ...
0
votes
0answers
16 views

Auto-generate unique sheets when you add a new row in Google Sheets?

I'm a fairly new user to Google Sheets, I'm trying to set up a sheet whereby when you add a new line in one sheet, it creates a separate check-list sheet. So when a new user is added to the master ...
3
votes
1answer
69 views

Calculate time difference within one cell

I'm trying to calculate the time difference and get the result 04:00. This obviously doesn't work because MINUS expects 2 arguments and instead gets a range. Generally, is there a way to output a ...
1
vote
1answer
33 views

Combine multiple IF statement/condition into one Google Sheet column

How can I achieve in Google Sheet so that the column will auto populate the correct "vendor" based on the selection on "model" and "size". Refer below: There will be 2 ...
0
votes
0answers
14 views

Can I create a Google Sheet macro to take the content from one cell and make it the hyperlink for another cell?

I do a report every month that has a column of Page titles and the next column is the URL. My boss wants the page titles to have the hyperlink applied to them so we can remove the hyperlink column. I'...
-2
votes
0answers
10 views

How do i give a certain cell a number value in google sheets? [closed]

I am not sure how to word this, but i am making a spreadsheet and wanted to know how to give a number value of 600,000 to B2. So if i put 3 into B2 it would be 1.8 million and then using B7 i could ...
0
votes
0answers
10 views

Change Values cell location from number of another cell

So I have =HYPERLINK("#gid=0&range=A2"; "TMI"&Status!B4&" - "&Status!C4&" - "&Status!I4). What I want to do is to change number 4, after,...
0
votes
0answers
12 views

Group By, multiple times?

I'm in the midst of creating some code to query my data. Here's an example of said data: Text version of image Name ToF AOF Jon Cena Buyer 1 Tim Brown Seller 3 Cabel Guy Buyer 5 Cabel Guy ...
1
vote
0answers
16 views

Pull data from separate Google Sheet into a Google Forms response sheet and update dynamically

I have a Google Sheets leave sheet for employees where we track the leave days due and days already used. I also have a Google Form which submits responses to linked sheet and runs with Form Publisher ...
1
vote
1answer
13 views

Making dynamic number of arguments based on a specific criteria for importrange function

I am counting number of cases per day in each month by importrange data from daily sheets in every month with this formula on D3 for example: =SUM(IMPORTRANGE($Q$5,R5),IMPORTRANGE($Q$6,R5),IMPORTRANGE(...
1
vote
1answer
22 views

How to use a query to find a row of same name but last entry, then copy a value next to it (update request)

(This is an updated version of this question - I am trying my best to make myself clearer this time.) I am trying to use a query to copy values from one column to another. Both source and destination ...
0
votes
1answer
12 views

How to extract desired text from a cell

As you can see below I have these data in single cell I want only car nos as my result
2
votes
1answer
58 views

How to use a query to find a row of same name but last entry, then copy a value next to it

I am trying to use a query to copy values from one column to another. Both source and destination columns have a name on their left columns, and the name must match. The source names could be ...
1
vote
1answer
17 views

FILTER between columns

I have thousands of data in 2 columns First one is ordered by export and the other by latest activity. The export columns has more queries than the latest activity. (can't change the export to the ...
1
vote
1answer
56 views

Google Spreadsheet Script Trigger: This app is blocked

I am trying to create a trigger for a script in google spreadsheet and upon trying to save the trigger I am getting the following error: This app is blocked This app tried to access sensitive info in ...
1
vote
0answers
19 views

Need help with google sheets, sending info from one sheet to another, but allowing the second sheet to be modifiable!

Guys for example I have this master sheet, which has one specific column that I want to filter by. for example it has different job titles, and I want that to be on another sheet so I can create "...
1
vote
1answer
17 views

How do I print a specific number of A based on a value from a cell in Google Sheets

There's a cell that would contain the number of times a letter would be printed in a cell. i.e. cell a1 = 3. b1 should look as "A A A" my rough script is: for(var arow=0; arow<4; arow++) {...
0
votes
0answers
13 views

Logic to auto populate cells and auto-check checkboxes

I'm in Google Sheets and would like to have a master document with rows indicating individual, non-duplicating course codes as the source of the primary record. Other columns (i.e. name, time, etc.) ...
0
votes
0answers
12 views

Allocate budgets into daily levels based on date range

Is there a way in Google Sheets to allocate cost/units evenly into daily breakouts if a total is entered with start date and end date? Ideally this would happen through a Google Sheets query. I've ...
0
votes
0answers
8 views

Conditions don't duplicate when I duplicate a sheet

I've created a progress report template with connections to grade level standards. I plan to duplicate the sheet for each student. However, when I duplicate the sheet, the connections to the list tab ...
0
votes
1answer
25 views

Check if Google Sheet cell contains string from range of cells

I have a spreadsheet with a list of job titles in column A and a list of specific words in column C (C2:C13) ('researcher' ,'scientist' for example). All I want is to fill column B with TRUE if the ...
0
votes
0answers
8 views

Google Finance automatic daily updating

I am creating an index that follows several tickers from 2014 to the present (daily data) by using Google Finance in Google Sheets. I want to add all of the tickers' new prices at the end of each of ...
0
votes
0answers
42 views

How to combine words from multiple cells with the REGEXMATCH command?

I want for example use the REGEXMATCH command to recognize when certain combinations of words is found in a specific cell, for example: =REGEXMATCH(C25;C29&D15) Let's say that in C25 there is the ...
0
votes
0answers
18 views

How to get a value in context of a random filled cell

I have a field which needs to get a random value in context of another random value. Let's say I have a random person row Person has a field with a favorite specific food. I want to generate a random ...

1
2 3 4 5
133