# 100,000 reference limit for big data sheet. How to save references?

Hi all,

I have a big data sheet - insurance claims.

When the client fills out the insurance claim, it writes the insurance number in forms, which then is used to search for data to be pulled from 11 other sheets according to this number.

I have approximately 35~ columns that need to be filled.

What I did is index every column with IFERROR formula:

=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(INDEX({1 - Sum}, MATCH([Certificate Reference]@row, {1 - Series + Number}, 0)), INDEX({2 - Sum}, MATCH([Certificate Reference]@row, {2 - Series + Number}, 0))), INDEX({3 - Sum}, MATCH([Certificate Reference]@row, {3 - Serija + Number}, 0))), INDEX({4 - Sum}, MATCH([Certificate Reference]@row, {4 - Serija + Number}, 0))), INDEX({5 - Sum}, MATCH([Certificate Reference]@row, {5 - Serija + Number}, 0))), INDEX({6 - Sum}, MATCH([Certificate Reference]@row, {6 - Serija + Number}, 0))), INDEX({7 - Sum}, MATCH([Certificate Reference]@row, {7 - Serija + Number}, 0))), INDEX({8 - Sum}, MATCH([Certificate Reference]@row, {8 - Serija + Number}, 0))), INDEX({9 - Sum}, MATCH([Certificate Reference]@row, {9 - Serija + Number}, 0))), INDEX({10 - Sum}, MATCH([Certificate Reference]@row, {10 - Serija + Number}, 0))), INDEX({11 - Sum}, MATCH([Certificate Reference]@row, {11 - Serija + Number}, 0)))

So this is 35 columns, with similar formulas, that pulls data from ~60,000 rows, and of course i am getting stuck with 100k references.

Is there a way to somehow save this project, do helper sheets in between for example, or somehow write a formula that pulls all corresponding cells according to first indexed value? Note - i can't just copy paste full row, because structure in final and indexed sheets is different.

• ✭✭✭✭✭✭

I hope you're well and safe!

Sometimes in my client solutions, when needed, I join values together in a so-called helper column in the Source sheet(s) and then break it up in the Destination sheet. The result is that the range decreases significantly.

Make sense?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

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

Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Hi, thank you for response.

If i understand your solution correctly, you are offering to add multiple cell values in the sheets indexed in combination like a "CSV" file with delimiters and then parsing them out in the final sheet?

That might work, however, this will take ages to do with 11 sheets (soon 13) and 35 columns combined from each sheet.

I am also interested in formula which parse the values, especially when there are text values inside that has a "company name" inside, which might range from 1 word to 5 and so on.

Wondering if we could come up with more simple solution.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!