index collect

HI All,

I have a formula that i have got working - only issue i have is I have run out of cross reference "space" and cannot add my last spring to complete my function

is there anther way to do this

Source sheet

OR date

UP date

Site

Target sheet

Required date

Updated Date

Site

i need to return source sheet UP date, when OR matches required and site

like i said the formula works - but cant add my last string -

IFERROR(INDEX(COLLECT({UP26}, {OR26}, [Date Required]@row , {SITE26}, [SITE HELPER 1]@row ), 1)

=IFERROR(INDEX(COLLECT({UP1}, {OR1}, [Date Required]@row , {site1}, [SITE HELPER 1]@row ), 1), IFERROR(INDEX(COLLECT({UP2}, {OR2}, [Date Required]@row , {SITE2}, [SITE HELPER 1]@row ), 1), IFERROR(INDEX(COLLECT({UP3}, {OR3}, [Date Required]@row , {SITE3}, [SITE HELPER 1]@row ), 1), IFERROR(INDEX(COLLECT({UP4}, {OR4}, [Date Required]@row , {SITE4}, [SITE HELPER 1]@row ), 1), IFERROR(INDEX(COLLECT({OP5}, {OR5}, [Date Required]@row , {SITE5}, [SITE HELPER 1]@row ), 1), IFERROR(INDEX(COLLECT({UP6}, {OR6}, [Date Required]@row , {SITE6}, [SITE HELPER 1]@row ), 1), IFERROR(INDEX(COLLECT({UP7}, {OR7}, [Date Required]@row , {SITE7}, [SITE HELPER 1]@row ), 1), IFERROR(INDEX(COLLECT({UP8}, {OR8}, [Date Required]@row , {SITE8}, [SITE HELPER 1]@row ), 1), IFERROR(INDEX(COLLECT({UP9}, {OR9}, [Date Required]@row , {SITE9}, [SITE HELPER 1]@row ), 1), IFERROR(INDEX(COLLECT({UP10}, {OR10}, [Date Required]@row , {SITE10}, [SITE HELPER 1]@row ), 1), IFERROR(INDEX(COLLECT({UP11}, {OR11}, [Date Required]@row , {SITE11}, [SITE HELPER 1]@row ), 1), IFERROR(INDEX(COLLECT({UP12}, {OR12}, [Date Required]@row , {SITE12}, [SITE HELPER 1]@row ), 1), IFERROR(INDEX(COLLECT({UP13}, {OR13}, [Date Required]@row , {SITE13}, [SITE HELPER 1]@row ), 1), IFERROR(INDEX(COLLECT({UP14}, {OR14}, [Date Required]@row , {SITE14}, [SITE HELPER 1]@row ), 1), IFERROR(INDEX(COLLECT({OP15}, {OR15}, [Date Required]@row , {SITE15}, [SITE HELPER 1]@row ), 1), IFERROR(INDEX(COLLECT({OP16}, {OR16}, [Date Required]@row , {SITE16}, [SITE HELPER 1]@row ), 1), IFERROR(INDEX(COLLECT({UP17}, {OR17}, [Date Required]@row , {SITE17}, [SITE HELPER 1]@row ), 1), IFERROR(INDEX(COLLECT({UP18}, {OR18}, [Date Required]@row , {SITE18}, [SITE HELPER 1]@row ), 1), IFERROR(INDEX(COLLECT({OP19}, {OR19}, [Date Required]@row , {SITE19}, [SITE HELPER 1]@row ), 1), IFERROR(INDEX(COLLECT({UP20}, {OR20}, [Date Required]@row , {SITE20}, [SITE HELPER 1]@row ), 1), IFERROR(INDEX(COLLECT({UP21}, {OR21}, [Date Required]@row , {SITE21}, [SITE HELPER 1]@row ), 1), IFERROR(INDEX(COLLECT({OP22}, {OR22}, [Date Required]@row , {SITE22}, [SITE HELPER 1]@row ), 1), IFERROR(INDEX(COLLECT({OP23}, {OR23}, [Date Required]@row , {SITE23}, [SITE HELPER 1]@row ), 1), IFERROR(INDEX(COLLECT({UP24}, {OR24}, [Date Required]@row , {SITE24}, [SITE HELPER 1]@row ), 1), IFERROR(INDEX(COLLECT({UP25}, {OR25}, [Date Required]@row , {SITE25}, [SITE HELPER 1]@row ), 1), "")))))))))))))))))))))))))

Find more posts tagged with

Accepted answers

All comments

Jgorsich

This approach looks a bit awkward, it seems like there HAS to be a more elegant way to achieve this that will also be more maintainable and fit your remaining criteria. Are all of your UP and OR ranges referencing different sheets or just 1 sheet? If just 1 sheet, a helper column on THAT sheet that pulls the relevant data into a single set of columns might be the answer.

Susan van Niekerk

cant do that - data needed from both sets of dates - i already used helper columns to join Site & Team :-0 but cant get around theses dates - have look everywhere for something easier but so far no luck

jjg279

Hey @Susan van Niekerk, I agree with @Jgorsich, there has to be a different way to do this… Maybe doing the collect on your original sheets then just referencing that column with all your criteria, or a HAS function… Could you share some screen shots of what you have and what you are shooting for? Hoping we can come up with something!!

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.