I am stuck with 25000 limit

SuzanneH · 02/03/20

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:

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).
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å · 02/03/20

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,[email protected])

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

工作流顾问/ CEO @WORK BOLD

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

SuzanneH · 02/04/20

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å · 02/04/20

Glad you got it working!

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

I am stuck with 25000 limit

Answers

Categories