Cell-link formula

Paul Newcome

Does you connector run automatically, or do you run it manually?

Kelly Gabel

So if you create a one-way workflow (in either direction) it's triggered by a manual button push. If it's bi-directional, it's automatically triggered by a change in either platform.

Paul Newcome

Which are you using?

Kelly Gabel

In this case bi-directional, but mostly because it's automated. There will be many updates happening regularly and having to always click that Run trigger isn't very scalable for us.

I have used the uni-directional ones before for other use cases successfully, but none of those requiring any cell linkage so that's why I am just now realizing this limitation.

Paul Newcome

Ok. I think I might have an idea or two. Still requiring some manual intervention, but it could be a little easier/faster.

But first...

Are your cell links laid out in the same shape/size on both sheets? For example...

You are linking a grid that is 4 columns wide and 12 rows long to a grid on the target sheet that is also 4 columns wide and 12 rows long?

If your cells are laid out in the same size and shape, you can create cell links "in bulk". On the target sheet you would highlight the 4x12 grid, follow the usual cell linking process to get to the source sheet, then select a 4x12 grid there, and all 48 of those cell links will be made at the same time.

Kelly Gabel

Well that is great to know, in general, but I'm afraid our use case isn't that simple. The source sheet is a program-level GANTT tracker housing data for multiple projects.

Potentially, the process you are describing could save SOME time as we have to manually update the links...but the sheer volume of how OFTEN we would have to make these manual updates to ensure that our data in Salesforce doesn't get stale is just not scalable.

I so appreciate your time on this issue, but I think that realistically, anything that requires us to have to reset the links at any frequency is just going to be bandwidth prohibitive.

Paul Newcome

How frequently is the connector running?

Paul Newcome

@Andrée Starå I was wondering if you might have any ideas...

@Kelly Gabel Please let me know if I missed anything below or misspoke.

Basically when the connector runs it removes all cell links and cross sheet references.

Problem:

Sheet A has A LOT of cell links/cross sheet references.

Sheet B is a consolidation of Sheet A that allows for fewer cell links/cross sheet references that need to be redone after the connector runs.

The connector cannot pull from a report.

Solution:

???

Kelly Gabel

@Paul Newcome , the sheet essentially runs constantly. Any time a mapped cell is changed, the connector runs. As our PMs are updating their data, it triggers the connector.

And yes, you explained the issue accurately. Thanks!

Paul Newcome

What kind of data are you pushing through the connector?

Would an option be to work the data in reverse where the PMs are updating a "master sheet" and then the data in the master sheet is being linked back to the individual project sheets?

I am not sure if the connector would remove OUTBOUND cell links as well?

A different way to explain it would be similar in theory to a setup that I built for my team. We have a project charter and a project plan. The project charter is linked out to a master metrics sheet for tracking certain milestones. Since those milestones are also on the project plan, the project plan includes cell links to the appropriate fields in the charter so the data is only entered once.

So you would have data on your master sheet that is updated by your PMs and that data is then linked out to the appropriate places in the individual project sheets. Does that make sense? Again... This is all dependent on the connector not also breaking outbound cell links.

Kelly Gabel

Hey :) So as I understand it, the connector essentially strips all formatting from the sheet with every trigger...don't think it will care if they are inbound or outbound.

Paul Newcome

Ugh. Of course it does. Well I am officially out of ideas. I'm sorry. Hopefully @Andrée Starå has encountered something similar and can provide some guidance...

Kelly Gabel

@Paul Newcome haha and now you understand my frustration! LOL Thank you so much for putting your experience and wisdom here so that I can be sure that I've exhausted all possible work arounds.

Paul Newcome

Happy to (try) to help. 👍️