Cell-link formula

04/20/21
Answered - Pending Review

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

Previous1

Answers

  • Paul NewcomePaul 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 GabelKelly 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 NewcomePaul Newcome ✭✭✭✭✭

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

  • Kelly GabelKelly 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 NewcomePaul 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 GabelKelly 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 GabelKelly 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 NewcomePaul Newcome ✭✭✭✭✭

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

  • Kelly GabelKelly Gabel ✭✭✭✭✭

    Thanks Paul!

  • Paul NewcomePaul 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 GabelKelly 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 NewcomePaul 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 GabelKelly Gabel ✭✭✭✭✭

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

  • Paul NewcomePaul 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 GabelKelly Gabel ✭✭✭✭✭

    haha exactly! I appreciate that!

Sign In or Register to comment.