1

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.

Currently

=filter(importrange("https://docs.google.com/spreadsheets/...60","Data!$A:$FH"),IF(REGEXMATCH(Temp!CP:CP, "Read"), 1, 0))

this formula works fine. The problem is that I would need to update that URL each day on multiple sheets.

I was hoping to be able to just paste the URL into a separate tab, and have importrange look at that cell to find the URL.

I was hoping that I could do something like this:

=filter(importrange(**School Sheets!$I$4**,"Data!$A:$FH"),IF(REGEXMATCH(Temp!CP:CP, "Read"), 1, 0))

Where 'School Sheets!$I$4' refers to the location on a different tab within this workbook where I would update the URL of the raw data each day.

That does not seem to be working.

Improve this question
4
  • Welcome to Web Applications. What do you mean by "That does not seem to be working"? Please describe the spreadsheet structure, including the relevant spreadsheet sites, like the spreadsheet locale, sample input data, and the expected result; if an error message happens, add the textual error message and what you have tried to solve the problem.
    – Rubén ♦
    May 17 at 17:32
  • One addition. The Regexmatch(...) portion in the example above is currently pointing to a tab that is identical to the sheet from the importrange. The correct code that works is =filter(importrange("...docs.google.com/spreadsheets/d/....60","Data!$A:$FH"),IF(REGEXMATCH(importrange("....docs.google.com/spreadsheets/d/....60","Data!CP:CP"), "Read"), 1, 0)) My goal is to change both places where it has the url embedded in the import range to a cell number that will have that url (since the url will change daily).
    – Brian Nussbaum
    May 17 at 17:35
  • @Wicket: thanks for looking at this. When I use the code listed above, it correctly pulls in 1696 lines over A:FH columns. When I change the "url" to a cell location it does not seem to work, and just says #ERROR! Formula Parse Error. with no further explanation.
    – Brian Nussbaum
    May 17 at 17:44
  • 1
    You are very welcome. Please bear in mind that comments are not intended to have discussions; they aim to ask for clarification and provide feedback; all the relevant details should be included directly in the question body. P.S. To learn the ropes of this community, please check out the tour and How to Ask.
    – Rubén ♦
    May 17 at 17:50

1 Answer 1

Reset to default
0

Formula

The following formula can be tweaked for your needs:

=FILTER(
   IMPORTRANGE(SchoolSheets!B1, SchoolSheets!B2),
   REGEXMATCH(
     IMPORTRANGE(SchoolSheets!B4, SchoolSheets!B5),
     "Read"))
NOTES:
  • The row count for both IMPORTRANGE functions must match or the FILTER function will throw a #N/A error. In my example I used rows 1-1000 for both. You can increase/decrease this or, if you are absolutely sure the row counts match, can use complete columns without specifying rows.
  • I removed the IF function from your FILTER condition (not needed):
    REGEXMATCH(range,regex) vs. IF(REGEXMATCH(range,regex),1,0)
  • The url and range values for each IMPORTRANGE(url,range) use cell references.
  • To allow the most flexibility for you, each url uses a different cell reference.
  • If, in practice, the Spreadsheet ID is identical, you can simply duplicate the values in the editable sections of SchoolSheets.  That is in fact what I have done in [SchoolSheets]: SchoolSheets!B10 has the data spreadsheet ID, and SchoolSheets!B14 has the filter sheets spreadsheet ID.

Demo Spreadsheet

Google Sheets link to demo: [FILTER & IMPORTRANGE (using cell references)].

You can freely edit the demo spreadsheet however so can anyone else with the URL. If you want to preserve it, you should save your own copy.

SHEET NAMES:

  1. Example - contains main formula
    Open [Example] in Google Sheets

     

  2. SchoolSheets - source for custom spreadsheet ID, target range, etc.
    Open [SchoolSheets] in Google Sheets

     

  3. Data - target data for IMPORTRANGE function
    Open [Data] in Google Sheets

     

  4. Temp - values used by the FILTER function
    Open [Temp] in Google Sheets

Improve this answer
2
  • Thank you. I am understanding why the IF is not needed now. However, still not quite working as I wish. The original post listed a reference to Temp! in the REGEXMATCH. That was a mistake on my part. In the comment I clarified that I am interested in both the IMPORTRANGE and the REGEXMATCH to point to the same cells with the url of the data source. Goal is something like this =filter(importrange(SchoolSheets!$I$4,"Data!$A:$FH"),REGEXMATCH(importrange(SchoolSheets!$I$4,"Read"))) but still not quite right. Thanks so much!
    – Brian Nussbaum
    May 19 at 12:41
  • 1
    Thank you! This did it! Much appreciated.
    – Brian Nussbaum
    May 19 at 20:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.