Concatenate for Cross Sheet Reference

Hello community,

I'm here again asking for help :)

I have a summary sheet that has status of all tasks across companies' project plans. I've got done the creation of a few cross sheet references for testing, these references targets those companies project plan sheets and its tasks progress percentage. The goal would be getting the task progress value changed as the dropdown menu with the company name is changed, changing the cross reference sheet value formula avoiding the creation of many summary sheets and then pulling the data to a dashboard.

Company1 Tasks

References for each company:

Dropdown menu (Column Project, row 6):

I tried to use the concatenate function but with no success, in the summary sheet I'm getting an #INVALID REF error. Formula used for it:

={Project6 + " - M365 Assessment"}

Thanks !

I'm open to suggestions 😉

Find more posts tagged with

Accepted answers

Georgie

Hi @Daniel King,

Anything between curly brackets {} is a cross-sheet reference, and these must either exist prior to using them within formulas, or can be created when you write or edit a formula, by selecting the cell(s) or column(s) in the sheet you’re referencing. If you edit a reference by typing values into it but without changing the selected cells in the reference, you’ll end up with #INVALID REF errors, as you’ve found.

To pull in the progress percentage from different sheets depending on the value in the Project column, you can use an INDEX MATCH formula with IF and CONTAINS. That would look something like this:

=IF(CONTAINS("Company1", Project@row), INDEX({Progress % Company 1}, MATCH(Task@row, {Company 1 Tasks}, 0)), IF(CONTAINS("Company2", Project@row), INDEX({Progress % Company 2}, MATCH(Task@row, {Company 2 Tasks}, 0)), IF(CONTAINS("Company3", Project@row), INDEX({Progress % Company 3}, MATCH(Task@row, {Company 3 Tasks}, 0)))))

You’ll need to set your references up so that:

{Progress % Company 1} references the entire % column in the Company1 sheet
{Company 1 Tasks} references the Task column in the Company1 sheet
{Progress % Company 2} references the entire % column in the Company2 sheet
{Company 2 Tasks} references the Task column in the Company2 sheet
And so on for all companies

If you need to reference more than 3 companies, you’ll need to repeat the following section within the formula, change the value that the CONTAINS function is looking for to the next company’s name, and set up the references for that company’s sheets:

IF(CONTAINS("Company1", Project@row), INDEX({Progress % Company 1}, MATCH(Task@row, {Company 1 Tasks}, 0))

The repeated section would always go after the "0))" at the end of the MATCH function, as you can see in the full formula above.

For more information on INDEX MATCH formulas, take a look here: Formula combinations for cross sheet references.

Hope that helps!

Georgie

All comments

Georgie

Hi @Daniel King,

To pull in the progress percentage from different sheets depending on the value in the Project column, you can use an INDEX MATCH formula with IF and CONTAINS. That would look something like this:

=IF(CONTAINS("Company1", Project@row), INDEX({Progress % Company 1}, MATCH(Task@row, {Company 1 Tasks}, 0)), IF(CONTAINS("Company2", Project@row), INDEX({Progress % Company 2}, MATCH(Task@row, {Company 2 Tasks}, 0)), IF(CONTAINS("Company3", Project@row), INDEX({Progress % Company 3}, MATCH(Task@row, {Company 3 Tasks}, 0)))))

You’ll need to set your references up so that:

{Progress % Company 1} references the entire % column in the Company1 sheet
{Company 1 Tasks} references the Task column in the Company1 sheet
{Progress % Company 2} references the entire % column in the Company2 sheet
{Company 2 Tasks} references the Task column in the Company2 sheet
And so on for all companies

IF(CONTAINS("Company1", Project@row), INDEX({Progress % Company 1}, MATCH(Task@row, {Company 1 Tasks}, 0))

The repeated section would always go after the "0))" at the end of the MATCH function, as you can see in the full formula above.

For more information on INDEX MATCH formulas, take a look here: Formula combinations for cross sheet references.

Hope that helps!

Georgie

Daniel King

Thanks @Georgie that was so helpful! I'm now able to run what I was trying to. 😉

Georgie

Glad I could help!

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.