'Save' cross-sheet references (reached limit)

System

This discussion was created from comments split from: Index Collect.

PeggyLang

@Genevieve P.
I have an issue where I have run out of 'Cross sheet references'. I currently have 100 and need 5 more!
I was trying to understand if I could replace the individual cross sheet references in a row with a Index/Collect or Index/Join function. Above I read that an Index/Join will return multiple values in a single cell. Is there any way to have those multiple values entered in adjacent columns of the same row?

Currently I have an Index/Match formula in each of the 3 cells of rows, see screenshot;

The source sheet has each of those components in adjacent cells on a row. ('Match' is store #-FYI.)

I'm wondering if I can 'collect' the 3 different bits of info and insert them into the 3 separate columns in screenshot 1. I'm trying to 'save' cross sheet' references.

Paul Newcome

@PeggyLang When I run into this, I will join multiple cells together with a formula, INDEX/MATCH that over to my target sheet, and then parse it out there. Basically what you are wanting to do but with a couple of extra steps in the middle. I have found that parsing is easier when I use UNICHAR delimiters in a specific order. Something along the lines of

=UNICHAR(8601) + [First Column]@row + UNICHAR(8602) + [Second Column]@row + UNICHAR(8603) + [Third Column]@row + UNICHAR(8604)

Notice my delimiters are UNICHAR functions for 8601, 8602, 8603, and 8604. This makes managing the parsing that much easier.

Now you can use an INDEX/MATCH to bring over this helper column that has the delimited string in it and use this to grab the first.

=MID([String Column]@row, FIND(UNICHAR(8601), [String Column]@row) + 1, FIND(UNICHAR(8602), [String Column]@row) - (FIND(UNICHAR(8601), [String Column]@row) + 1))

To pull the second piece from the string, change the UNICHAR functions from 1, 2, 1 to 2, 3, 2. The third piece from the string would be 3, 4, 3. The basic idea is that the first UNICHAR is the "piece number" you want to pull. This coincides with the fact that it is in the "character to start with" portion of the MID function. Then to tell the MID function how many characters to pull, we subtract the start of the first piece from the start of the second piece.

So boiled down it would look like this:

=MID([String Column]@row, start of first, start of second - start of first)

And using those specific UNICHAR functions to start, delimit, and end the string makes it much easier to just copy/paste/tweak to get the next piece from the string.

PeggyLang

@Paul Newcome
That's a GREAT solution I hadn't thought of.
In the interest of time (have to present to client this afternoon) the team paired down the data somewhat so I was then within the 100 cross sheet reference max.
I will definitely keep your solution in my toolbox though.

THANKS!!!

Paul Newcome

Happy to help. 👍️

I actually use this method quite frequently even if I am not concerned with going over the limit of cross sheet references. If I am pulling over a lot of data from a reference sheet such as entering a store number and returning the address, postal code, country, manager, assistant manager, store type, etc., I will go ahead and use this method to bring it all over into a column called "Store Data" and parse it out. Even if I know I won't hit 100, it drastically cuts down on the number of formulas using cross sheet references which will help with sheet speed on larger and/or more complex sheets, and it looks a little cleaner without having the blue arrows next to so many cells.

Andrée Starå

https://community.smartsheet.com/discussion/comment/478882#Comment_478882

Hi Paul,

I hope you're well and safe!

Using UNICHAR is a fantastic idea. Have you tested using Emojis instead?

Thanks!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

Paul Newcome

@Andrée Starå Yes. But the UNICHAR functions are much easier to update quickly when you are parsing out a lot of fields. I can just change the numbers within the functions, and I know those numbers coincide with which piece I am pulling from my string.

With emojis, I would need to keep a lit up on another screen so that I can see if the smiley face starts my second or third string, and what ends the fourth string etc. etc. and then copy/paste the emojis after copy/pasting the formula itself.

The UNICHAR functions are just a bit easier to use at scale.

Andrée Starå

https://community.smartsheet.com/discussion/comment/478898#Comment_478898

I considered using the numbers as emojis, but that is a good point. UNICHAR makes more sense.

Thanks!

PeggyLang

@Paul Newcome
Ok, you've convinced me. Tomorrrow when my brain is fresher, I'm going to try your way. Right now the sheet is taking quite a bit of time to save as there are 100 cross sheet references.
May take a bit of trial and error as I'm unfamiliar with UNICHAR as yet.
I'll let you know how it goes. :)

PeggyLang

@Andrée Starå
How do you use 'emojis'?

Paul Newcome

@PeggyLang Happy to help. 👍️

Using emojis can be thought of as copy/pasting a tiny picture in between the strings. Instead of just changing a 1 to a 2 or a 2 to a 3, you would need to copy/paste each different tiny picture in. Using the UNICHAR function is much easier to both set up and manage.