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

Tommy Barba

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.

Andrée Starå

@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å

Hi @Tommy Barba

How many columns would the string be if it was split in the "CRM" sheet? Is it only 8 columns?

One workaround to still use column formulas would be to add the string to the sheet summary section and reference it from there.

Make sense?

I hope that helps!

Be safe and have a fantastic week!

Best,

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

✅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!

Tommy Barba

Hi Andrée! I think I have 12 data in that string. That works fine, but now as we have the new feature to make a formula into a "column formula" (great!) I would like to make those 8 data we have in 8 columns to be "Column Formula" avoiding them to be missing when people add new rows.

How do I reference a Sheet Summary to a cell in the sheet?

Andrée Starå

@Tommy Barba

Yes, I got that, and if I understand your structure, we could use the sheet summary section for it, and then the column formulas would work.

Feel free to reach out directly! You have my number. 😉

Leibel S

@Tommy Barba

I do not entirely understand your issue, but it seems as though you want to make a direct reference to specific rows using column formulas only.

The way to do this is by creating a column with row numbers. Below is a system i use all the time for this:

Add the below 2 helper columns:

1. "LINE-ID" : Auto Number Column
2. "ROW#" : Column Formula: "=MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)

If you can send post snapshots of your current setup and/or current formulas I can help you convert them to column formulas.

Andrée Starå

@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!

Tommy Barba

Thanks Adrée, for the workaround, or better solution, to use Sheet Summary for the CRM data string and therefore ability to create Column Formulas!

Andrée Starå

@Tommy Barba

You're more than welcome!

God Jul! < Merry Christmas in Swedish