Let's say you only wanna select the first column. Cannot, it says 'NO_COLUMN: A'.
=query({O2:P4;O8:P9},"select A")
It is easy to select ALL columns.
=query({O2:P4;O8:P9},"select *")
use:
=QUERY({O2:P4;O8:P9}, "select Col1")
so I have a formula inside an array that looks like this
={CRYPTOFINANCE("kraken:"&ARL8&"/USD", "price_history", "10d")}
I want to auto fill the array with the same function but with the 'ARL8' reference in an ascending order, just like auto filling cells, but keep it all in one cell it should look like this...
={CRYPTOFINANCE("kraken:"&ARL8&"/USD", "price_history", "10d") ; CRYPTOFINANCE("kraken:"&ARL9&"/USD", "price_history", "10d") ; CRYPTOFINANCE("kraken:"&ARL10&"/USD", "price_history", "10d")} ,SUBSTITUTE(TRANSPOSE(SPLIT(REPT(12,1050),2)),1,"MISTAKE")) etc.
the thing is that I have about 1000 values from ARL8 reference to ARL1008 reference so it will take really long for me to write it all manually, so is there a way I can keep the rest of the function but have the ARL cell reference dynamically written while being able to specify the length of the array?
It's something I am struggling for a while now and still couldn't find a way so I really appreciate if you could explain the solution as well, and if there is more info I can give let me know, and thanks.
I tried a few things but they gave me different errors I didn't know how to solve and others just didn't work, I will just put it in case it helps
=ARRAYFORMULA(CRYPTOFINANCE("kraken:"&ARL8:ARL1008&"/USD", "price_history", "10d"))
the error it gave me is - "error Attribute price_history isn't supported in batch formulas"
try:
=BYROW(ARL8:ARL20, LAMBDA(x, CRYPTOFINANCE("kraken:"&x&"/USD", "price_history", "10d"))
you can generate a formula with a formula like:
={""; INDEX("=ARRAYFORMULA({SPLIT(""Exchange,Base,Quote,Time,Open,High,Low,Close,Quote Volume,Base Volume"", "","")"&
QUERY(";QUERY(TO_TEXT(CRYPTOFINANCE(""kraken:""&"&C2&
SEQUENCE(C3, 1, C4)&"&""/USD"",""price_history"",""10d"")), ""offset 1"", )",,9^9)&"})")}
I believe what I am trying to do should be simple in Google Sheets formulae, but any solution based on an Excel formula should be easily transferable.
Because additional characters will be added periodically, I have a named range: "Heroes".
| Heroes |
|---|
| Bilbo |
| Gandalf |
| Saruman |
| Wormtongue |
| Tom Bombadil |
For each hero, I have a worksheet in one overall workbook. On these worksheets, there are columns for Date, Time, Quest, and Count. Several times per day, a hero will venture out on a quest of a certain type, returning with a certain count as a prize. Each venture has its own row distinguishable by date and time. Eg-:
| Date | Time | Quest | Count |
|---|---|---|---|
| 12/4 | 3:00P | Ring | 9 |
| 12/5 | 8:00A | Mordor | 6 |
| 12/5 | 4:15P | Sting | 3 |
Meanwhile, I have a summary worksheet, on which I am manually entering (for now... bonus points to help create an =arrayformula() or equivalent to grab all unique date/time combinations from each character's worksheet) the date and time at which one or a batch of heroes are sent to quest. I am trying to figure out the formula template that will sum the counts for each quest type for each hero at the specific date and time signified by its corresponding row (starting at 12/4, 3:00P, Ring, the count should be 9, for example, which is Bilbo's prize for questing at that time; of course, other heroes are also sent out at 3:00P, resulting in prizes for the other quests, and multiple heroes may venture on the same type of quest at any given time):
| Date | Time | Ring | Sting | Mordor | Moria |
|---|---|---|---|---|---|
| 12/4 | 3:00P | 9 | 3 | 4 | 1 |
| 12/4 | 9:30P | 1 | 0 | 8 | 0 |
| 12/5 | 8:00A | 5 | 3 | 6 | 9 |
| 12/5 | 12:10A | 3 | 1 | 3 | 8 |
| 12/5 | 4:15P | 4 | 5 | 2 | 5 |
Since not every date and time in the summary sheet will exist on each hero's worksheet, I seem unable to use "SUMIFS", which functions in such a way that each sum_range and criteria_range are added on only across the same row when conditions are met. I think there is a SUMPRODUCT(), or INDEX(MATCH()) way to do this, but when including the named range to read across multiple worksheets, only the first hero's numbers were added in my tinkering with this.
I'm dancing around the solution here. Anyone care to tango ? Many thanks !
Sample Workbook for support: https://docs.google.com/spreadsheets/d/142IE9r2ip6YHsGdMr-zt_IHd6W7glqUId_UiGQnCUZs/edit?usp=sharing
it would be done like this:
=QUERY({Bilbo!A:D; Gandalf!A:D; Saruman!A:D; Wormtongue!A:D; 'Tom Bombadil'!A:D},
"select Col1,Col2,sum(Col4) where Col1 is not null group by Col1,Col2 pivot Col3", 1)
if you want a specific order of places you can do:
=TRANSPOSE(SORT(TRANSPOSE(QUERY(
{Bilbo!A:D; Gandalf!A:D; Saruman!A:D; Wormtongue!A:D; 'Tom Bombadil'!A:D},
"select Col1,Col2,sum(Col4) where Col1 is not null
group by Col1,Col2 pivot Col3", 1)),
MATCH(FLATTEN(QUERY(QUERY(
{Bilbo!A:D; Gandalf!A:D; Saruman!A:D; Wormtongue!A:D; 'Tom Bombadil'!A:D},
"select Col1,Col2,sum(Col4) where Col1 is not null
group by Col1,Col2 pivot Col3", 1), "limit 0", 1)),
{"Date"; "Time"; "Ring"; "Sting"; "Mordor"; "Moria"}, ), 1))
or manually like this:
=QUERY(QUERY({Bilbo!A:D; Gandalf!A:D; Saruman!A:D; Wormtongue!A:D; 'Tom Bombadil'!A:D},
"select Col1,Col2,sum(Col4) where Col1 is not null group by Col1,Col2 pivot Col3", 1),
"select Col1,Col2,Col5,Col6,Col3,Col4")
if you thinking to outsmart it with the list of Heroes... don't. referring a range from other sheets requires the usage of INDIRECT. and surprise surprise, INDIRECT is not supported under ARRAYFORMULA so you cant build an array. at this point, you either re-think your life choices or you use a script where there is support for such indirected arrays. the best you can do without script is to hardcode it like:
=QUERY({
INDIRECT(Main!A2&"!A:D");
INDIRECT(Main!A3&"!A:D");
INDIRECT(Main!A4&"!A:D");
INDIRECT(Main!A5&"!A:D");
INDIRECT(Main!A7&"!A:D")},
"select Col1,Col2,sum(Col4) where Col1 is not null
group by Col1,Col2 pivot Col3", 1)
and ofc this will only work if sheet exists on the list and list does not contain empty cells otherwise you will get ARRAY error like this because Main!A6 sheet does not exist:
so to counter it we can do some slide of hand tricks with IFERROR which will allow us to not get the error and still use non-existent sheets and even empty cells so we can pre-program it for future additions like this:
=QUERY({
IFERROR(INDIRECT(IF(Main!A2="", 0, Main!A2)&"!A:D"), {"","","",""});
IFERROR(INDIRECT(IF(Main!A3="", 0, Main!A3)&"!A:D"), {"","","",""});
IFERROR(INDIRECT(IF(Main!A4="", 0, Main!A4)&"!A:D"), {"","","",""});
IFERROR(INDIRECT(IF(Main!A5="", 0, Main!A5)&"!A:D"), {"","","",""});
IFERROR(INDIRECT(IF(Main!A6="", 0, Main!A6)&"!A:D"), {"","","",""});
IFERROR(INDIRECT(IF(Main!A7="", 0, Main!A7)&"!A:D"), {"","","",""});
IFERROR(INDIRECT(IF(Main!A8="", 0, Main!A8)&"!A:D"), {"","","",""});
IFERROR(INDIRECT(IF(Main!A9="", 0, Main!A9)&"!A:D"), {"","","",""});
IFERROR(INDIRECT(IF(Main!A10="", 0, Main!A10)&"!A:D"),{"","","",""});
IFERROR(INDIRECT(IF(Main!A11="", 0, Main!A11)&"!A:D"),{"","","",""})},
"select Col1,Col2,sum(Col4) where Col1 is not null
group by Col1,Col2 pivot Col3", 1)
note: 4 columns in range A:D = 4 empty cells {"","","",""}
I'm trying to add a QUERY by copying the individual formatting of each cell from one worksheet to another. Someway? thank you!
unfortunately, this is not possible to do with QUERY or any other formula. using a script is the only option you have.
transferring multiple individual colors from in-one-cell to another cell is not possible at this current version of the Google Sheets (nor at any previous versions):
Posts
All Comments
