I am stuck with 25000 limit

SuzanneH

Hello.

I am trying to summarize data from some smartsheets into another smartsheet. I moved from VLOOKUP to INDEX MATCH. However, I have hit the 25000 error again using the formula below:

=INDEX({DIGITAL RAIL 52 Weeks}, MATCH($Description@row, {Digital RAIL Description Only}, 0), 2)

What I need to do is increase the 2 on the end to 3, then 4, then 5... all the way to 65.

I have about 50 lines for which I need to do this. I hit the 25K error after 10 lines.

Questions:

1. Can I return the results as an array, so I am not using the reference on every cell? (Note that the data I am pulling has the same column structure).
2. If the answer is no, how can I go about this? (Cell linking is very tedious and I believe will be the same issue).

I am happy to share my smartsheets with whoever can help me.

Thanks,

Suzanne

Andrée Starå

Hi Suzanne,

That sounds strange! It sounds like you shouldn’t be close to the limit at all.

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer!

SuzanneH

Hi Andree,

I believe I was able to overcome the error with cell linking (after I sent this question I kept trying). However, I am not sure if it is the best method. The INDEX MATCH issue is this:

For each line in my summary sheet, shown above, I have a column for every week of the year. In each cell, I have this formula:

=INDEX({DIGITAL RAIL 52 Weeks}, MATCH($Description@row, {Digital RAIL Description Only}, 0), 2)

and then each week incresments that last number by one... for example:

=INDEX({DIGITAL RAIL 52 Weeks}, MATCH($Description@row, {Digital RAIL Description Only}, 0), 3)

=INDEX({DIGITAL RAIL 52 Weeks}, MATCH($Description@row, {Digital RAIL Description Only}, 0), 4)

The smartsheet I am linking to looks like this

And I have reports running off the above smartsheet for users to enter data into the white rows while the grey rows are calculated. When they make a change, the % sold is updated for that line and I want it to update that line on my summary sheet. (The summary sheet only has % sold lines).

In the end, I will make this a bit more complex because I want to have two types of entries... true entries which are sales in contract and "on hold". So I want the user to indicate it is a hold, I'm thinking by typing in "(H)" or "HOLD" and then I exclude that when I calculate the sold number (and store the HELD % in a new row). But all of this will only happen once I finalize the %SOLD method on the summary sheet.

I'll share with you now.

Andrée Starå

Glad you got it working!

I'll take a look as well and get back to you if I have any tips/tricks!