1

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"

player0
  • 124,011
  • 12
  • 67
  • 124
boten500
  • 123
  • 1
  • 1
  • 8

1 Answers1

2

try:

=BYROW(ARL8:ARL20, LAMBDA(x, CRYPTOFINANCE("kraken:"&x&"/USD", "price_history", "10d"))

update

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)&"})")}

enter image description here

demo sheet

player0
  • 124,011
  • 12
  • 67
  • 124
  • the thing is my function results in data table each and when there is another cell that contains a formula below it both the data table and the cell overlapped cancelling the array, when I was typing the function manually (example is in the question) I could solve it by adding ; between each formula so I want to ask you, when I use BYROW range does it separate each array with ;? because every array appears 1 row below. I tried that formula nevertheless but it gave me an error and it's probably due to my function. still is it possible to do it all in a single cell? – boten500 Oct 22 '22 at 13:10
  • @boten500 can you screenshot the output of one formula? – player0 Oct 22 '22 at 13:16
  • yeah here https://snipboard.io/UFuav6.jpg – boten500 Oct 22 '22 at 13:39
  • 1
    @boten500 sry, I meant your formula `={CRYPTOFINANCE("kraken:"&ARL8&"/USD", "price_history", "10d")}` – player0 Oct 22 '22 at 13:40
  • here https://snipboard.io/ebZcoU.jpg the column number is fixed but the row number isn't it's changable when changing the last arguement which is in this case "10d" – boten500 Oct 22 '22 at 13:51
  • damn I really appreciate your work thank you, can you explain me what is this part for ```INDEX("=ARRAYFORMULA({SPLIT(""Exchange,Base,Quote,Time,Open,High,Low,Close,Quote Volume,Base Volume"", "","")"&``` and this ``, ""offset 1"", )"`` also it seems I can't copy the formula with the TO_TEXT function as it only takes the first cell and converts it to text, it looks like this https://snipboard.io/ButA8r.jpg if you could tell me the parts in the formula that need this function I will try to fix it myself and again thank you for the help! – boten500 Oct 23 '22 at 16:56
  • 1
    @boten500 did you check out demo sheet? if you want to use the formula from your last picture you need to wrap it into ARRAYFORMULA otherwise TO_TEXT will output only top left cell. the SPLIT part is for headers because that "offset 1" removes them so you wouldn't end up having headers row every x rows sandwiched into the dataset – player0 Oct 23 '22 at 17:41
  • 1
    oh yea now it's working before that I was copying only my formula it's working amazingly thank you so much! – boten500 Oct 23 '22 at 19:01
  • Hey I have a question regarding the solution you gave me, I am asking it as a comment here because It's related to the solution you gave me. so before I was retrieving data of every single cryptocurreny between rows ARL8-ARL1008, the thing is sometimes the retreiving doesn't work because one or more cryptocurrencies' data won't get pulled resulting in "array literal" error, I already fixed this issue by replacing the errored zone where the data was supposed to be with blank, the question is how can I replace it instead with the cell reference of the cryptocurrency that resulted in error? – boten500 Nov 06 '22 at 17:01
  • https://snipboard.io/uV2AkL.jpg this is how it looks right now and where I want it to be – boten500 Nov 06 '22 at 17:01
  • 1
    @boten500 try: https://i.stack.imgur.com/kotse.png – player0 Nov 06 '22 at 18:36
  • I think you misunderstood me, sorry, what I meant is instead of the errored cell being blank, I want it to give the cell reference of the cell that gave an error, for example if it's cell ARL11 that gave the error, ARL11 reference will replace the error, or it could also be the number of the cell in the C3 range, similiar to this https://snipboard.io/oBOp5X.jpg the end goal for me is to identify the name (cell reference) of the cryptocurrency that gave an error. – boten500 Nov 06 '22 at 20:30
  • 1
    @boten500 try: https://i.stack.imgur.com/n8a5v.png – player0 Nov 06 '22 at 20:46