Linking within 2 google sheets

[DavidG]

I have a google sheet with all my business expenses, different vendors and companies, I am looking for a way to create a new sheet for each vendor and somehow pull from the main sheet only the lines that says their name in it.
Any way to do that?

[Knaper Yaden]

How proficient are you in Google Sheets functions? You can accomplish this using the =Query and =IMPORTRANGE functions.

Example (to put in the first cell, i.e. A1 or A2)

=Query(IMPORTRANGE("1w-3MCszsR00MBa9XsMaFTgNnrzDmfhfhfllWeLw","Expenses!A2:F"),"Select Col1, Col2, Col3, Col4 where Col2='Vendor Name'"

• Replace 1w-3MCszsR00MBa9XsMaFTgNnrzDmfhfhfllWeLw with the source workbook’s URL or Key (the key is the long random middle part of the URL)
• Replace Expenses!A2:F which the source sheet name and range of data
• Add/remove all columns from the range that you want to import: Col1, Col2, Col3, Col4
• The column number (in the source sheet that has the vendor names) and name of the Vendor Col2='Vendor Name'"

Makes sense? Works?

[DavidG]

Colum A:A are all the vendors so we can have A1 being ‘Graphic designer’ A2 ‘Bookkeeping’ and then A3 goes back to ‘Graphic designer’
so how can I import only the line that cell A of that line is ‘Graphic designer’ ?

[DavidG]

I get this error
Error
Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: Col2

[Knaper Yaden]

@DavidG

What is the range you used? Post your full function.

[DavidG]

@Knaper-Yaden
=Query(IMPORTRANGE("xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx","Main!A:A"),"Select Col1, Col2, Col3, Col4 where Col3='bookkeeping'")

[Knaper Yaden]

@DavidG said in Linking within 2 google sheets:

@Knaper-Yaden
=Query(IMPORTRANGE("xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx","Main!A:A"),"Select Col1, Col2, Col3, Col4 where Col3='bookkeeping'")

That’s simple… if your range is A:A, which is a single column, how can there be a Col2 to pull data from?..

How many columns is your total data? If you have data until column F your range should be Main!A:F, and the Select should have Col1, Col2, Col3, Col4, Col5, Col6

[DavidG]

ok now I get
Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "where" "where "" at line 1, column 32. Was expecting one of: "true" ... "false" ... "date" ... "timeofday" ... "datetime" ... "timestamp" ... "min" ... "max" ... "avg" ... "count" ... "sum" ... "no_values" ... "no_format" ... "is" ... "null" ... "year" ... "month" ... "day" ... "hour" ... "minute" ... "second" ... "millisecond" ... "with" ... "contains" ... "starts" ... "ends" ... "matches" ... "like" ... "now" ... "dateDiff" ... "quarter" ... "lower" ... "upper" ... "dayOfWeek" ... "toDate" ... <ID> ... <INTEGER_LITERAL> ... <DECIMAL_LITERAL> ... <STRING_LITERAL> ... <QUOTED_ID> ... "(" ... "-" ... "min" ... "max" ... "count" ... "avg" ... "sum" ... "year" ... "month" ... "day" ... "hour" ... "minute" ... "second" ... "millisecond" ... "now" ... "dateDiff" ... "lower" ... "upper" ... "quarter" ... "dayOfWeek" ... "toDate" ... "(" ... <STRING_LITERAL> ... <DECIMAL_LITERAL> ... <INTEGER_LITERAL> ... "-" ... "true" ... "false" ... "date" ... "timeofday" ... "datetime" ... "timestamp" ... <ID> ... <QUOTED_ID> ... "min" ... "max" ... "avg" ... "count" ... "sum" ... "no_values" ... "no_format" ... "is" ... "null" ... "year" ... "month" ... "day" ... "hour" ... "minute" ... "second" ... "millisecond" ... "with" ... "contains" ... "starts" ... "ends" ... "matches" ... "like" ... "now" ... "dateDiff" ... "quarter" ... "lower" ... "upper" ... "dayOfWeek" ... "toDate" ...

this is my script
=Query(IMPORTRANGE("xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx","Main!A:D"),"Select Col1, Col2, Col3, Col4, where Col3='bookkeeping'")

[Knaper Yaden]

Extra , after Col4