Doc1: I have individual sheets setup for each Game Week which list the football matches being played on that particular week.
Doc2: I have an individual sheet setup for each team.
I'm looking to pull the scores and team names from a certain row in Doc1 to Doc2 based on the team in Doc2.
I've worked out how to do this manually but with 20 teams and 38 Game Weeks that is a lot of copying, pasting and changing 30,000+ cells to alter the column reference in.
The "Manual" code I've got working is:
=importrange("https://docs.google.com/spreadsheets/d/xxxxxx/edit#gid=xxxxxx","Game Week 1!A1")
I'd like to be able to set the team name in Doc2 and automatically fill in the Game Week rows based on than name.
After some time researching I've come up with this:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/xxxxxxxxxx/edit#gid=xxxxxxxxxx","Game Week 16!A1:A19"),B1,"Select * Row1,Row5")
But this seems to only want to return numbers and not text as it gives me the error message:
...Error
Function QUERY parameter 3 expects number values. But 'Select * Row1,Row5' is a text and cannot be coerced to a number.
Any help would be great, as I am at a bit of a dead end.
Below are screenshots of the "Game Week 1" sheet set up and the individual "team" sheet.
Update 1 on the Code:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/xxxxxxxxxx/edit#gid=xxxxxxxxxx","Game Week 20!A:E"),"Select Col1, Col2, Col3, Col4, Col5 Where Col3, Col5 matches "B1&"")
This seems like a little step in the right direction but is throwing up an error:
"Error Formula parse error."
Update 2:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/xxxxxxxxxx/edit#gid=xxxxxxxxxx","Game Week 1!A:E"),"Select Col1, Col2, Col3, Col4, Col5 Where Col3, Col5 matches '"&B1&"'")
The latest tweaks seem to have had some forward progress but I am now receiving this error message:
Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " <ID> "Col3 "" at line 1, column 43. Was expecting one of: "(" ... "(" ...
Tinkering with a few bits, it seems to be after the 'where' part. As soon as it reads the Col3, Col5
part it seems to mess up.
Update 3:
Realized a small mistake on my part, I was searching Col3, Col5 instead of Col1, Col5. This still didn't work but then I removed the Col5 so just looking at Col1 and this has returned the correct value.
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1dMo6F1CTxAaEx1bS2NEu4kIu5hJTCBbCt13AV2E_f2w/edit#gid=1390527531","Game Week 1!A:E"),"Select Col1,Col2,Col3,Col4,Col5 Where Col1 Matches '"&B1&"'")
Not sure why I can't check two columns as I've seen other peoples work performing this, may look at changing the whole code to an IF statement(?).
Update 4:
CRACKED IT!
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/xxxxxxxxxx/edit#gid=xxxxxxxxxx","Game Week 1!A:E"),"Select Col1,Col2,Col3,Col4,Col5 Where Col5 Matches '"&B1&"' or Col1 Matches '"&B1&"'")
Thanks to Rubén for his valuable input.
**
in B1? What it means / what do you expect as result?