/docs/community?hl=en
This content is likely not relevant anymore. Try searching or browse recent questions.
How to reference Arrayformula result as array, not value 1 Recommended Answer 7 Replies 2 Upvotes
1 Recommended Answer
$0 Recommended Answers
1 Relevant Answer
$0 Relevant Answers
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,
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!
Relevant Answer Relevant Answers (0)
All Replies (7)
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
marked this as an answer
Relevant Answer
Lance,

Thanks for the reply! D19:D will work, but I would rather not reference the entire column since it would make it difficult to put other information underneath in lower rows. 

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
marked this as an 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
 
 
marked this as an 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
marked this as an 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.

I'll let you know what I find out!

Matt
marked this as an 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
marked this as an answer
This question is locked and replying has been disabled.
Discard post? You will lose what you have written so far.
Write a reply
10 characters required
Failed to attach file, click here to try again.
Discard post?
You will lose what you have written so far.
Personal information found

We found the following personal information in your message:

This information will be visible to anyone who visits or subscribes to notifications for this post. Are you sure you want to continue?

A problem occurred. Please try again.
Create Reply
Edit Reply
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.
Report abuse
Google takes abuse of its services very seriously. We're committed to dealing with such abuse according to the laws in your country of residence. When you submit a report, we'll investigate it and take the appropriate action. We'll get back to you only if we require additional details or have more information to share.

Go to the Legal Help page to request content changes for legal reasons.

Reported post for abuse
Unable to send report.
Report post
What type of post are you reporting?
Google takes abuse of its services very seriously. We're committed to dealing with such abuse according to the laws in your country of residence. When you submit a report, we'll investigate it and take the appropriate action. We'll get back to you only if we require additional details or have more information to share.

Go to the Legal Help page to request content changes for legal reasons.

Reported post for abuse
Unable to send report.
This reply is no longer available.
/docs/threads
//accounts.google.com/ServiceLogin
You'll receive email notifications for new posts at
Unable to delete question.
Unable to update vote.
Unable to update subscription.
You have been unsubscribed
Deleted
Unable to delete reply.
Removed from Answers
Marked as Recommended Answer
Removed recommendation
Undo
Unable to update reply.
Unable to update vote.
Thank you. Your response was recorded.
Unable to undo vote.
Thank you. This reply will now display in the answers section.
Link copied
Locked
Unlocked
Unable to lock
Unable to unlock
Pinned
Unpinned
Unable to pin
Unable to unpin
Marked
Unmarked
Unable to mark
Reported as off topic
/docs/profile/0