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 😉
Best Answer
-
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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thanks @Georgie that was so helpful! I'm now able to run what I was trying to. 😉
-
Glad I could help!
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 432 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 76 Community Job Board
- 504 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!