This content is likely not relevant anymore. Try searching or browse recent questions.
How to reference Arrayformula result as array, not value
I'm trying to reference the result of a FILTER expression as an array, but instead of giving me the array, it just gives me the first value of the filter. In the following sheet,
https://docs.google.com/spreadsheets/d/1ai4KGItxjqfboWQ3qxSZjs9je5beryqvRRYZnTa-U4I/edit#gid=107293152
I attempt define the arrayformula in cell C19 using the array result from D19:
C19 = ARRAYFORMULA(...D19...)
D19 = FILTER(...)
where I want C19 to use the actual list returned by FILTER in D19. Only one value, however, is returned. If, instead of referencing "D19" in the formula of C19, I just copy and paste the formula for D19 there directly, the arrayformula works as expected. But I'd rather not do that since I want to be able edit D19 without having to make the same edit in C19 as well (it also seems inefficient). Replacing "D19" with "D19:D40" also works, but the length of the D19 array is expected to change over time, and I don't want to have to manually adjust the range of D19:D40 every time that it changes. So my question is this: is there any way to reference the array result of an arrayformula (or filter, or anything that returns an arrayformula-like result) from another cell?
Any suggestions or alternative ideas are appreciated.
Thanks!
Latest Update Latest Updates (0)
Recommended Answer Recommended Answers (1)
Recommended Answer
Relevant Answer
Hi Andy
First of all, we LOVE Flatten(). It's a game changer. I guarantee if you prowl this forum, you'll start to see it used a lot!
Next, gotcha. I've modified my formula. It now uses the number of items (CountA) in the subCat list to define the range. Take a look.
Lance
recommended this
marked this as an answer
Recommended based on info available
Our automated system analyzes replies to choose the one that's most likely to answer the question. If it seems to be helpful, we may eventually mark it as a Recommended Answer.
Relevant Answer Relevant Answers (0)
Our automated system analyzes the replies to choose the one that's most likely to answer the question.
All Replies (7)
Recommended Answer
Relevant Answer
D19:D, and "open ended reference", will reference the entire column, allowing you to add data as needed.
Other than that, if you'd like more help, I'd prefer to have you describe what you need to do with the data, rather my trying to work through existing formulas. (too hard for my rattled brain). If you want help with your formula, someone else may be able to assist. If you want help with your data problem, I'll do my best, or ask for help when I don't have a solution.
Best,
Lance
recommended this
marked this as an answer
Recommended based on info available
Our automated system analyzes replies to choose the one that's most likely to answer the question. If it seems to be helpful, we may eventually mark it as a Recommended Answer.
Recommended Answer
Relevant Answer
Lance,
Here's an explanation of what I'm trying to do:
In D19:D40 I've flattened the 2D list of subcategories defined above into a 1D column (e.g. {a,b;c,d} -> {a;b;c;d}). To the left, in C19:C40, I want to show the category that each subcategory belongs to. To the right, I want to have "Items" as a sort of subsubcategory. So, just like how I list out the subcategories for each category horizontally, I want to list out the items in each subcategory horizontally in E19:40. This way I can keep track of the subcategory and categories of each item.
It might be easier to start with an array of {category,subcategory,items...} instead, but I was hoping to work out this method work as well.
Thanks,
Andy
recommended this
marked this as an answer
Recommended based on info available
Our automated system analyzes replies to choose the one that's most likely to answer the question. If it seems to be helpful, we may eventually mark it as a Recommended Answer.
Recommended Answer
Relevant Answer
Hi Andy,
I created a copy of your tab and put a formula in C19. (Gree)
I used D19:D41, a limited range, but one cell beyond your active data. If you add new data WITHIN the range - that is, above row 41 (Grey), the formula references will update.
I think there's another item on your wish list. If what I've done so far is right, I'll see if I can help with the other thing.
btw, what is FLATTEN() ? Where did you get it? What does it do?
Best,
Lance
recommended this
marked this as an answer
Recommended based on info available
Our automated system analyzes replies to choose the one that's most likely to answer the question. If it seems to be helpful, we may eventually mark it as a Recommended Answer.
Recommended Answer
Relevant Answer
Dear Lance,
FLATTEN makes a 2D list into a 1D list like so:
{a,b,c;
d,e,f}
->
{a;
b;
c;
d;
e;
f}
The odd thing about FLATTEN is that Google doesn't recognize it as a built-in function, but allows you to use it anyway. I discovered it by accident when I was trying to write a custom FLATTEN function myself, and found myself wondering why my own FLATTEN function wasn't working correctly (the name was already reserved).
Thanks for the solution, but unfortunately, because D19:D40 is defined via arrayformula, the formula in C19 actually won't automatically update if the size of the D19 arrayformula changes. For example, I added "a" and "b" into "Miscellaneous" in a duplicate sheet and C19 did not update accordingly.
Thanks,
Andy
recommended this
marked this as an answer
Recommended based on info available
Our automated system analyzes replies to choose the one that's most likely to answer the question. If it seems to be helpful, we may eventually mark it as a Recommended Answer.
Recommended Answer
Relevant Answer
Hi Andy
First of all, we LOVE Flatten(). It's a game changer. I guarantee if you prowl this forum, you'll start to see it used a lot!
Next, gotcha. I've modified my formula. It now uses the number of items (CountA) in the subCat list to define the range. Take a look.
Lance
recommended this
marked this as an answer
Recommended based on info available
Our automated system analyzes replies to choose the one that's most likely to answer the question. If it seems to be helpful, we may eventually mark it as a Recommended Answer.
Recommended Answer
Relevant Answer
Hey Andy,
Amazing discovery! I wonder how long this has existed behind the scenes in Google Sheets.
I've reached out to our Product Expert contacts at Google to try to get a little more information about it and make sure that it's here to stay before I begin using/recommending it to others on the forum and elsewhere.
Amazing discovery! I wonder how long this has existed behind the scenes in Google Sheets.
I've reached out to our Product Expert contacts at Google to try to get a little more information about it and make sure that it's here to stay before I begin using/recommending it to others on the forum and elsewhere.
I'll let you know what I find out!
Matt
recommended this
marked this as an answer
Recommended based on info available
Our automated system analyzes replies to choose the one that's most likely to answer the question. If it seems to be helpful, we may eventually mark it as a Recommended Answer.
Recommended Answer
Relevant Answer
Dear Lance and Matt,
Lance, that solves the problem! It's also good to hear that FLATTEN will be useful. Matt, thank you for the reply as well! I'm surprised that there are hidden functions like FLATTEN which even you guys don't know about--I wonder if there are other ones which are similarly not recognized as built-in functions?
In any case, thanks for all the help. I'll mark Lance's latest reply as the solution.
Thanks,
Andy
recommended this
marked this as an answer
Recommended based on info available
Our automated system analyzes replies to choose the one that's most likely to answer the question. If it seems to be helpful, we may eventually mark it as a Recommended Answer.
This question is locked and replying has been disabled.
Link to post
Delete post?
This will remove the reply from the Answers section.
Notifications are off
Your notifications are currently off and you won't receive subscription updates. To turn them on, go to Notifications preferences on your Profile page.
This reply is no longer available.
Badges
Some community members might have badges that indicate their identity or level of participation in a community.
