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

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

@Paul Newcome

Appreciate your assistance.

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

Thanks Paul.

Paul Newcome

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

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

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.

Vivien Chong

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

Thank you so much.

Paul Newcome

Excellent. Glad we got it working. 👍️