Create Column Formulas with data from other sheets using minimum # cross-references

We have one data sheet with multiple data from our CRM system in a long text string (to save cross-references). The string is split up in each sheet to individual columns, and copied down to all tasks by formula. Secondly we have another datasheet with "department" which we cross-reference into every sheet.

The new feature "Column Formula" is great but it doesn't allow formula to distinct cell => cross-reference. But which way is the most efficient creating least cross-references?

2500 sheet and adding 300 per year.

Each cross-referenced data will be applicable to in average 200 tasks per sheet.

8 columns with data from the CRM dataset that we want to make "Column Formulas"

1 column with data from the Function belonging (department) that we want to make "Column Formula"

How do we set this up to have the lowest possible number of cross-references? Or at least well below the limit of 100.000??

CRM data can be made as a normal table instead of long text string, if that helps.

Best Answer

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    @Tommy Barba

    Glad we got it working!

    For everyone else. We solved it using the Sheet Summary section to get the value string from the "CRM" sheet and then reference that Sheet Summary field in the Column Formula in the sheet.

    Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: | | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!