Linking within 2 google sheets


  • 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?


  • 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?


  • 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’ ?


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


  • @DavidG

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


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


  • @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


  • 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'")


  • Extra , after Col4

Suggested Topics

2
Online

370
Users

291
Topics

1.5k
Posts

Copyright © 2020 | info@shutfim.com