Ive created a google form for my scouthers, the problem is it puts all the data into one spreadsheet. So i created a second spreadsheet, that is supposed to import data from the first one, but only if the team number matches the value in A1. Ive found this, but im having trouble figuring out how to interpret this part of the function:
SELECT Col1 WHERE Col1 = 'Permanent'"
Anyone have any ideas?
So in other words, you want to move all data from one spreadsheet to another if the value in a specific column is “_____”?
This is my function.
=QUERY(IMPORTRANGE(“1VpSDUKkyTK8sZ1AztqELxGbLSpqesl0N6aBDyCq827w”, “B2:I”),“SELECT Col4 WHERE Col4 = ‘3581’”)
importing B2:I if the team number (in column 4 of the imported data) is 3581.
Edit: Error message: Query completed with an empty output.
I’m assuming that it does have something in the columns you want to import?
=QUERY(IMPORTRANGE(“url”,“Sheetname/range”),"select (Columns you want to import) where (column to select from) = ‘team number’)
Try this
Ill try that, we are leaving for lunch, ive got a chromebook on the bus.
Good luck
When you say you created a second spreadsheet, do you mean an entire separate document? Or just a different sheet within the same “workbook” (if we use excel terms)? If the other sheet is within the same workbook, you can skip using the importrange function (instead just directly referencing the sheet as shown below).
I helped my team get a similar sheets based scouting system set up this year, and the formula for getting all the entries from the form responses looks like this:
=query(data!A2:AD310, CONCAT(CONCAT("select * where D = ", B1), "order by C"))
In our spreadsheet, B1 is the team number in the same sheet as this formula is located.
In the form responses, column D contains the team number, and column C contains the match number. The “data” sheet contains the responses to the form.
One thing I noticed when setting up our system this year is that the format of the query parameters changed slightly. While using “Col4” or “Col3” used to work in previous years, we had to change to referencing columns by their letters in that query string. Maybe that’s contributing to your problem?
Do you really need ALL the data in a second sheet/tab?
If you just want to find values, like max or average, for a scoring data you can set up calculations that pull data from another sheet.
I have 500+ rows of data from our scouts, and doing a data filter on columns can isolate a team, but doesn’t allow for calculations. So I created a cell (A2 on my sheet) on a second sheet where I can type in a team number. In another cell I’ve entered this formula:
=AVERAGEIF(Sheet1!$C$2:$C$1000,$A$2,Sheet1!K2:K1000)
Here C2:C1107 is the range where team numbers reside
And K2:K1000 is where the cargo ship cargo scoring data resides.
So, IF range C2:C1000 has the value A2 (the desired team #), sheets averages the values in column K.
I’ve done this for all scoring values, and also used SUMIF with the same parameters to find total scored.
If you type a new team number into A2, it updates all the formulas.
I’m not importing all the data, Im only importing rows with the correct team number. in the end its no more than 15 rows.
Okay, i wasnt able to get this up and running for dalton, maybe the Championship.
Here is my original sheet, filled in by a form:
your “A2:I” needs a final row count for the I column.
It does not. Having no upper limit leaves it limitless. I have tested this with other functions, having no upper limit means that there will be no limit to the data it chooses from. also when writig this it gave visual feedback of what it was referencing, and that was correct.
I think you’ve got the wrong column in your query statement. It looks like the team number is actually in column E, so maybe that formula should be:
=query(ScoreSheet!A2:I, CONCAT(CONCAT("select * where E = ", A1), "order by B"))
I cant believe i didn´t see that. It appears to work. Ill have to run a few tests, but when i perfect it ill make it publicly available.
EDIT: it actually works, it just takes a minute to update, but that is because of some settings i implemented to help cut down on data usage.
This topic was automatically closed 365 days after the last reply. New replies are no longer allowed.
