Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

  • Employee
    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

  • Employee
    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

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

  • Employee

    Glad I could help!

    Need more information? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

  • I have a pretty basic sheet that I am using to develop a formula, and am encountering an issue I haven't seen before. The formula isn't including my second row for some reason. It isn't a huge issue f…
    User: "jjg279"
    Answered ✓
    9
    2
  • I'm sure that this is an easy fix but I've tried a bunch of different ways and can't get this to work. I need to get the max date from a sheet to feed it into my meta data sheet (dates are stored in d…
    User: "susanmgfin"
    Answered ✓
    8
    2
  • I need help to come up with the risk formula. I tried many different formulas and tried to modify it but just can't seem to have desired results. So these are the conditions I MUST meet: Program is a …
    User: "Ronak"
    Answered ✓
    29
    6