How can I remove a sheet reference, so I do not receive the alert ‘Reference a total of 25000 cells

2»

Comments

  • Vivien Chong
    Vivien Chong ✭✭✭✭✭✭

    HI @Paul Newcome

    I was trying to do the above as well due to the 25,000 cell limits.

    However I encounter problem in the Target Sheet. When trying to break up the JOIN data, I encountered "#INVALID VALUE".


    Target Sheet Column: Employee ID | JOIN | Name | Branch | Company

    My formula in the Branch column: =LEFT(SUBSTITUTE($JOIN@row, JOIN($Name@row:Name@row, "/") + "/", ""), FIND("/", SUBSTITUTE($JOIN@row, JOIN($Name@row:Name@row, "/") + "/", "")) - 1)

    Result: #Invalid Value

    The same goes to the other columns:

    Formula in Company column: =LEFT(SUBSTITUTE($JOIN@row, JOIN($Name@row:Branch@row, "/") + "/", ""), FIND("/", SUBSTITUTE($JOIN@row, JOIN($Name@row:Branch@row, "/") + "/", "")) - 1)


    Have I missed out anything? Appreciate your assistance.

  • Vivien Chong
    Vivien Chong ✭✭✭✭✭✭

    @Paul Newcome

    Appreciate your assistance.

    I am attaching a screenshot for the abovementioned for your easy reference:


    Thanks Paul.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 02/20/20

    @Vivien Chong The solution here is based on the name already being populated either manually or by a different formula (such as an INDEX/MATCH pointing at the Employee ID).


    To test... Try manually entering "Vivien Chong" into Name1. If that removes the error, then you can try something along the lines of this in the Name column:

    =LEFT(JOIN@row, FIND("/", JOIN@row) - 1)


    EDIT: The above is based on the assumption that the formula in the JOIN column is pulling based on the Employee ID column.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Vivien Chong
    Vivien Chong ✭✭✭✭✭✭

    @Paul Newcome

    I still couldn't get the right formula. I am sorry, I am rather slow with formula.

    For the name, I got it right with the formula you gave, but the others are still unable to retrieve the right information.


    And YES, I am using the Employee ID as a pulling factor in the JOIN column.


    Appreciate your assistance.

    Thanks.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I am going to have to look more into the Branch column, but The Company and Department columns have the wrong formulas.


    You should put the LEFT/SUBSTITUTE formula that references $Name@row:Name@row in the Branch column then dragfill to the right. So in the Company column it would read $Name@row:Branch@row and in the Department column it would read $Name@row:Company@row.


    What is the exact formula (copy/paste from sheet) that you have in the JOIN column? I do notice that the data in your screenshot has spaces on either side of the / . I am not sure if that may be part of it, but that may be a starting point.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Vivien Chong
    Vivien Chong ✭✭✭✭✭✭

    @Paul Newcome You are right, when I changed the " / " to "/", it works.

    Thank you so much.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Excellent. Glad we got it working. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!