Questions tagged [importrange]
This tag should be used to differentiate questions about the importation of a range of data from other questions on this site. Use when the topic is the IMPORTRANGE function. Should generally be used in conjunction with a tag for a specific app.
238
questions
0
votes
1
answer
17
views
Data from second importrange function is in the wrong column
When I import 2 ranges that are each in Columns A to L in their respective sheets, the second range is being returned in Columns M to X.
How can I fix it so the data from both ranges is returned in ...
1
vote
2
answers
32
views
Importrange Query importing correct OR statements, but not refining by date
I have a formula pulling relevant columns from a different document. It is working well except for the last part regarding the dates. It is seemingly ignoring that part of the formula and returning ...
- 31
2
votes
1
answer
59
views
ImportRange Query Formatted Incorrectly
I have a spreadsheet that I want to pull data from another spreadsheet.
This is my formula:
=query(importrange("https://docs.google.com/spreadsheets/d/14lj6yPp8h9U1JAEA4dz-uROaQgZxy60NjXniHOFUeAU/...
- 31
0
votes
1
answer
89
views
IMPORTRANGE sum column with condition
=SUM(IMPORTRANGE("1kWMBhk0T423p411K5_5sKOMQFdAsEDapdasYyr9gZj8","Oct!N2:N") "SELECT Col16, where Col16 contains 'Paid' )")
I need to calculate only the total of paid ...
- 11
0
votes
1
answer
128
views
Return only those rows with a date on or before today using Query and Importrange
In Google Sheets I have a QUERY where I want to import only those rows where the dates in Col23 are on or before today's date.
This is my current formula:
=QUERY(
IMPORTRANGE(
"...
- 11
1
vote
1
answer
124
views
Downloading CSV results in empty (#ref) file. Is it a problem with IMPORTRANGE? [duplicate]
I have a multi sheet workbook in Google Sheets. I need to export a compilation of tables from this workbook to CSV. I therefore have added a new sheet where I use IMPORTRANGE to combine values from ...
- 1
1
vote
1
answer
36
views
How to sum data on one row from multiple selected Google Sheets
I am tracking paid advertising key performing metrics on both Google & Facebook ads platforms on two separate Google Sheets, however, I'd like to combine both datas collected into one master sheet....
- 11
2
votes
1
answer
373
views
Looking up two criteria in a table, one vertical, one horizontal and finding a value
I have a large table of data.
Column A is names
Columns C-I are monthly commissions. (Column C is March 2023, Column D is April 2023, etc)
Columns C-I are going to change regularly as we are pulling ...
0
votes
0
answers
14
views
How to use IMPORTRANGE function [duplicate]
I’m just having an issue. I need to IMPORTRANGE two Google sheet tables, but in the main source table is a filter turned on. I need to connect data from column "D" with name RM and ...
- 1
0
votes
2
answers
5k
views
Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: D
I have data in a sheet called "PASTE HERE" that I want to import into another sheet in the same workbook. I don't want to use IMPORTRANGE because then copying the workbook would necessitate ...
- 1
3
votes
1
answer
660
views
Does importrange not work on Google Sheets embedded XLSX files?
The ImportRange documentation mentions nothing about not supporting XLSX embedded files.
But when I try it, it claims it has no permission (whereas I'm an editor in the file I try to import).
Does it ...
- 1,250
2
votes
1
answer
79
views
IMPORTRANGE and INDIRECT combination error
I am trying to dynamically import data from another Google sheet (sheet2), to current Google sheet (sheet 1).
In sheet 1, I would like to input the starting cell A2 and ending cell D20 in cells B2 and ...
- 23
1
vote
1
answer
243
views
Google Sheet ImportRange embedded in filter with RegexMatch option
I have a complicated spreadsheet with multiple tabs that filter for various things. The raw data comes from a file that I must download as a .csv file each day. Looking for a way to simplify a bit.
...
0
votes
2
answers
2k
views
What's the workaround to copy the link for a cell when using IMPORTRANGE?
I want to know what the correct way is to use IMPORTRANGE while it also gets the correct link for another spreadsheet. What ends up happening is that the link directs to the current file instead of ...
- 101
0
votes
1
answer
96
views
IMPORTRANGE to refer the function but not the value/result
There are 5 different spreadsheets, i.e. Master, 1, 2, 3, 4)
All cell A1 in 5 different spreadsheets have the same function e.g. =count(B1:B5), they count the data in their own spreadsheet.
And I want ...
- 1
0
votes
2
answers
423
views
Can you do an importrange on data from multiple sheets if the data itself has an arrayformula with a textjoin?
I'm trying to summarize a bunch of sheets into one via a query([(importrange)(importrange)(etc.)}. I've done this multiple times without issue, but now the data I'm pulling is has a columns that is ...
- 1
1
vote
2
answers
100
views
Query+Importrange for multiple sheets based on values in Col26
=QUERY({
importrange("1HGW2VJFyhosCM-5AK7II6CRt19sPMsmGPbxEJvPVnK0","SUN!A3:AB5001");
importrange("1Fjbl3Ndh-thsb9ufNhJ2GgHjjIfvG1e7vUe4dNcwCew","SEA!A3:...
1
vote
1
answer
1k
views
How to create Google sheets range off of 2 separate columns
I need to create a range off of two separate columns within a sheet.
Let's say column A is my index, while column D is third party index.
When using VLOOKUP i can lookup my index to find 3rd party ...
- 11
2
votes
1
answer
157
views
Using QUERY with IMPORTRANGE as the data source not allowing referencing columns
(Link to Test Spreadsheet)
I am working on a spreadsheet that has a list of people, and for each person, they have ten choices that correspond to adjacent columns with artist and song name. The data ...
- 23
0
votes
1
answer
124
views
RC notation in IMPORTRANGE
Is there a way to use RC notation in the IMPORTRANGE function? What I need to do is to import a certain column, which I'm finding using MATCH in the first row (headers)
=importrange("linktosheet&...
- 9
0
votes
1
answer
43
views
How to get additional row data to stay with row when linked to another spreadsheet
I have data in columns A:C of Spreadsheet 1 being pulled from Spreadsheet 2 via =importrange. Columns D:Z of Spreadsheet 1 is just text related to the imported data:
Spreadsheet 1 pulls start/end ...
0
votes
1
answer
30
views
How to build a table of imported data from conditional data sources
I have many spreadsheets where data will be added manually and which are all identical. Let's call these UserInputSpreadsheets.
Inside each user spreadsheet are tabs with data which are also identical....
- 101
2
votes
1
answer
1k
views
How to use IMPORTRANGE across discontinuous columns? (Or, how to omit columns?)
How are discontinuous columns imported? Here, the import is inclusive from B3 to D7 as:
Here, the formula is:
=importrange("https://docs.google.com/spreadsheets/d/...
- 716
3
votes
2
answers
163
views
Combine different sheets with importrange for dynamic data from google forms
I have 4 identical google sheets that get continuously filled with data from 4 different google forms. I want to merge the 4 resulting sheets into one Final Sheet. Moreover I want to be able to ...
- 39
1
vote
2
answers
572
views
Updating IMPORTRANGE based on date
I have a spreadsheet I have created with multiple tabs for users to input data on Wednesday of each week. I have a cover worksheet with an IMPORTRANGE formula to pull the data from the users sheets so ...
- 11
1
vote
0
answers
40
views
How do I filter in IMPORTRANGE using SEQUENCE and ARRAYFORMULA?
I am having issues with including a FILTER option in my current working formula listed below:
=QUERY (IMPORTRANGE("1n7qMGR9p8xD-8tyPIhfrcyBEsW8FAUMJMHmgxM7xIVI","Consultant Directory!A6:...
- 11
1
vote
0
answers
30
views
Add more conditions to the formula
=query({IMPORTRANGE("1c6ihzJjzD_Nhbz5rB1s0485PS98V-Q4bfVAiIon5vSA","MC2!A11:S5000")},
"select Col19, Col1, Col2, Col3, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, ...
- 11
1
vote
0
answers
164
views
Get the url-sheet from an other cell in Importrange
I am looking for a way to change the url in an importrange without actually opening the formula, but just changing the url in an other cell.
I tried with putting the cell reference, with the hyperlink ...
- 11
0
votes
1
answer
40
views
How do I relative the cell value only?
For example I have a sheet and there is a col with cell-like A2 and A2 has a hyperlink. A2 value is 'Apple' and the hyperlink is 'stackexchange.com'.
When I am trying to use importrange formula with ...
- 9
0
votes
0
answers
264
views
Importrange changes the format only for a couple cells
I am using importrange and for some cells google sheets changes the format of only a couple cells. I have attached a screenshot. The original worksheet has the correct dates M/DD/YYYY. So I am ...
- 1
0
votes
0
answers
264
views
Using Query & Import Range function to import data range with a date filter
I have been able to use the the Query and Import Range function perfectly.
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/13eoPsqGik4ZWFGPURrC9RYdlOuxdbCbQWXzWA9LC1L4/edit#gid=0",...
0
votes
1
answer
290
views
Countifs between 2 dates from an import range
Looking for some help on a Countifs formula from an import range (if this is the right thing to use(?))
I have a sheet tracking rejected bookings on certain dates, and I want to pull this into another ...
- 3
0
votes
1
answer
567
views
Indirect ► Transpose ► Importrange = It is not a valid cell/range reference - not an expert, need help
I'm trying to get a range from another spreadsheet, to appear depending on an ID number placed in the current sheet, I've been doing that for other data within the sheet and spreadsheet, but whenever ...
- 11
0
votes
0
answers
115
views
IMPORTRANGE contains specific columns with multiple answers
Example, one person can enter 5 events with several options for participating (both days or Sunday only or 1st go....) how do I get master1 sheet to only show contestants on Saturday and master2 only ...
0
votes
0
answers
136
views
IMPORT RANGE PARSE ERROR
I have this arrayformula where I need to match cells in a column from one worksheet to another and I used Importrange to link these two but it's giving me errors and I'm not sure what i'm missing
here ...
- 1
-1
votes
1
answer
101
views
Import a working formula from a different Google Sheet
I want to import a working formula from doc 1 to doc 2.
I used importrange but then it importet the data from doc 1 to doc 2 and not the formula.
I then used a second spreadsheet in doc 1 to show the ...
- 1
0
votes
3
answers
3k
views
Importrange with multiple criteria
Looking to countifs data from another workbook with multiple criteria.
So far I have
=COUNTIFS(IMPORTRANGE("URL", "Data save!$A$2:$A$5011"),">=01/04/2022")
This ...
0
votes
1
answer
410
views
How do I get importrange and jointext to combine 3 name cells into 1 cell on a new worksheet in Google sheets?
On Wksht A I have cells C5=last name (i.e. Doe), D5=first name (i.e. Jane), E5=first name (i.e. John). This is to cover 2 joint owners (i.e. husband and wife) in our housing complex. I want to import ...
- 11
-2
votes
1
answer
74
views
i can't find the problem to this formula
=UNIQUE(QUERY({IMPORTRANGE("1xWx1FRfvCfmvYi9Sb7Gh5lIAb-NAt0RG0h-e_HKmxjU","APP Directory!A2:U");IMPORTRANGE("1xWx1FRfvCfmvYi9Sb7Gh5lIAb-NAt0RG0h-e_HKmxjU","APP ...
- 1
1
vote
1
answer
577
views
Drag IMPORTRANGE formula and auto fill multiple cells
I import multiple cells by a checkbox with this formula:
=IF(A1=True;ARRAYFORMULA(IMPORTRANGE("URL";"Sheet1!A1:A10"));"")
But I'm looking for a solution to be able to ...
- 19
1
vote
1
answer
359
views
Google sheets import columns based on date value
Google sheets for simplicity's sake say I have two tabs, one with master data and one that is a search function to display a certain part of the master data. The issue is I only want import certain ...
- 11
0
votes
1
answer
885
views
google sheet how to import cells based on value in different cells
So I have two sheets.
Sheet A tab X contains two columns, first one with an url for a google doc and in the other column text IDs.
Sheet B tab Y contains a column with text ID of several more files ...
- 3
1
vote
1
answer
30
views
How to combine/Link values of Spreadsheet A onto B while preserving data added on B Sheets against data coming from A?
There are two spreadsheets that are used by two teams, Team A and Team B. Team A provides directions and posts work orders to Team B. Team B in turn enters comments on the actions completed. Each team ...
- 111
0
votes
1
answer
40
views
How to show the data from another spreadsheet but only the latest week
I have 2 spreadsheet, the first one consists of all of the data of withdrawal and deposit of items. The second spreadsheet is where the summary of the data shown.
Here is the master data link:
https://...
- 105
1
vote
1
answer
1k
views
Google Sheets Query with importrange by date range
Trying this Formula:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/18r5cARjmlRsbb0VRaAS6kAMkJm1p2qe0LsPCtYLS1xA/edit?resourcekey#gid=561906440","Form Responses 1!A:X"),...
- 11
0
votes
1
answer
45
views
importrange on google shhet
I have a question regarding the importrange() function. This is my formula:
=ImportRange("1u0TjjekVHHb6shYiSWoo0W1ygxuSu2tVvLstnEE1NFo";"Juillet!I5")
I have about 20 rows of data ...
1
vote
1
answer
27
views
Need help filtering entire rows from reference sheet to master sheet where column text meets criteria [closed]
I feel like this should be a relatively simple issue but everything I write is clearly incorrect or leaves me needing to do work manually every time new data is made.
Basically I have a series of ...
- 11
-1
votes
1
answer
492
views
Can I convert an entire sheet into all caps when data is imported using IMPORTRANGE?
I am using a form to collect data from customers. After submitting the form and collecting the data in Form Responses 1 (name of Google Sheet), I've created another sheet which imports most data, not ...
0
votes
1
answer
379
views
How to modify Query Options no_format to allow some format, but not others?
I have to replicate a Google Sheet, and am using ImportRange and Query together. I need Query also because the first sheet has urls under them, which I cannot have on the copied sheet. I was able to ...
- 1
1
vote
2
answers
1k
views
Appending / Adding manual data input to end of IMPORTRANGE dataset
I am trying to figure out how to append / enter manual data to the end of an IMPORTRANGE list. In my spreadsheet, each tab is a month. I want to IMPORT the prior month's list (rows) and then be able ...
- 21