Cell-link formula

Kelly Gabel

@Paul Newcome Hey there! Hope you are doing well!

Are you aware of any formula that mimics the cell link feature? I've already confirmed that I won't be able to use cell link and I'm hoping there might be another way.

Thanks!

Paul Newcome

Cross sheet references can be used, but exactly how to use them is going to depend on the data you are pulling and how you want it displayed. If you would like to provide more details, I would be happy to help see if we can figure something out.

Kelly Gabel

Hey! Thanks for the quick reply! So, essentially we are pulling regularly updated date fields from program-based GANTT schedules to a sheet that is connected to the Salesforce connector. Then the data is pushed over to Salesforce. The problem I've discovered is that when I use the cell link feature to aggregate the data to the connected sheet, it resets the sheet (meaning removes all cell links) every time the connector is triggered. I've already confirmed that is a limitation of the connector...so I know I'm not doing anything wrong there, but the only suggestion I was given was to purchase yet another premium app (Data Mesh). That's not going to go over well here so I'm trying to exhaust all other possibilities first.

I have a call with our Customer Support team next week but trying to get a jump on my research.

Paul Newcome

Have you tested yet to determine whether or not cross sheet references will break similar to how the cell links do?

Kelly Gabel

No, I haven't. I am not familiar with how to do a cross sheet reference that simply copies a cell over.

Is it a formula?

I would love to try it. Although I'm concerned the same issue will arise and it will reset it simply to the text value when the connector is triggered...

Paul Newcome

I meant anything with a cross sheet reference in general such as a basic SUM function or an INDEX/MATCH or VLOOKUP or something.

To create a cross sheet reference that mimics a cell link, you can pick any function you want, and then when you get to the range, create the cross sheet reference. Then you would remove the function and leave the reference.

=SUM(

create your reference to the cell in the other sheet and you end up with

=SUM({Range Name})

Now remove the function so that you are left with

={Range Name}

Kelly Gabel

Ah! I wasn't aware you could do that! Great tip! I will give this a try and test with the connector. Thanks!

Kelly Gabel

Well, now I see how that works (cool!) but unfortunately, same result. It seems they have really made this impossible to do without the Data Mesh tool.

Thanks for the suggestion!

Paul Newcome

Hmm... I'll see if I can think of any ideas/workarounds.

Kelly Gabel

Thanks Paul!

Paul Newcome

So when the connector grabs the data from the sheet to send it to salesforce, it removes all links including cross sheet references, so we need a way to populate the sheet without using those.

Are you sourcing data from multiple sheets?

Kelly Gabel

Exactly. It essentially resets it to value only, no formatting.

Well, originally it was going to be from multiple sheets but I've decided it will be less maintenance to just be from one sheet...unfortunately, that sheet has a TON of formulas powering it, so I can't simply connect the connector to the sheet itself.

Paul Newcome

I don't have access to the connector, so let me ask this... Can it pull from a report instead of a sheet?

Kelly Gabel

Nope, just a sheet. A report would be such an easy solution, right?!

Paul Newcome

Apparently it would be too easy of a solution. Haha. I'll try to keep thinking on it, but I don't have much in the way of ideas at the moment.

Kelly Gabel

haha exactly! I appreciate that!