INDEX COLLECT over multiple documents

I am looking for your help once again :)

So the below formula works fine for me however I have other sheets that I also need to reference. I'm assuming I would need to add an AND or OR formula to the below however I'm not sure where or what structure the formula should be. And its to reference another 4 sheets, including the one mentioned below:

=INDEX(COLLECT({Sheet 1 - Range 1}, {Sheet 1 - Range 2}, HAS([Split Values]@row , @cell )), 1)

Thanks in advance.

Find more posts tagged with

collect

INDEX

Accepted answers

All comments

SueinSpain

Sorry no answer just an opinion here - I prefer simple formula to complex ones so would use helper fields to look up the 4 individually and then add them together as reuired

e.g. index(collect one in a field, then index(collect another to another field and then merge, compare etc. as reuired

I do this for 3 reasons (in my head)

a) to make each formula more simple to write

b) to make each formula simple to understand when I have to review at a later date and have forgotten the details of how I got to a complicated solution

c) to make easier for colleagues to pick up and understand should they need to amend in my absence

As I say just an opinion and sorry not a solution

Good luck

Debbie Sawyer

If all the sheets are the same column names etc, then you could create a report to pull all the data into 1 place, then we have an app called Smarter Report to Sheet that would send the collated data to a new destination sheet, then your index(collect() statement here would work for all combined sheets and wouldn't need updating when new sheets are added into the mix.

Reach out if you are interested in finding out more.

Alternatively you could concatenate your statements with delimiters?

=INDEX(COLLECT({Sheet 1 - Range 1}, {Sheet 1 - Range 2}, HAS([Split Values]@row , @cell )), 1) + ", " +

INDEX(COLLECT({Sheet 2 - Range 1}, {Sheet 2 - Range 2}, HAS([Split Values]@row , @cell )), 1) + ", " +

INDEX(COLLECT({Sheet 3 - Range 1}, {Sheet 3 - Range 2}, HAS([Split Values]@row , @cell )), 1)+", "+

INDEX(COLLECT({Sheet 4 - Range 1}, {Sheet 4 - Range 2}, HAS([Split Values]@row , @cell )), 1)

Kind regards
Debbie

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.