speed of sheet

L_123

I solved it never mind.  Thanks if anyone was looking at this.

 

I have a sheet that is moving very slowly, in fact the entire page doesn't seem to work when I have the sheet open. It is less than 30 columns and 100 rows, however it has many lookups and column references due to the nature of what I am trying to do. below is a sample of the code which is repeated down 3 columns for 24 total work groups (I've shown 5 of the workgroups). I haven't found any other way to do what I want and this works perfectly in getting me the numbers I desire, but the speed makes it almost un-useable. 

 

What the formula does- Searches for 2 values which I've concatenated into a helper box and returns a 3rd value on the same row. Repeats for each area in the specified facility, then averages them together and posts. The form is updated via webform and is not updated in a specific order, hence the column lookups, so I can use the sort function and have my worksheet be organized.

I'm still on a temporary account, but we have purchased some users and I will have the full access within the next couple days if that effects the speed at all.

I'd appreciate any feedback to be able to speed this sheet up. I'd prefer to keep it on a single page as I am going to draw a lot of data from this sheet to other sheets, but if it isn't possible I might end up having to use multiple sheets. 

 

 

 

=IF($Department1 = "1. Production PL17", AVG(LOOKUP("1. Production PL17" + " " + [Audit Cycle (date)]1, $concatenation:$[Shine Average], 19, false), LOOKUP("2. DF Dieshop" + " " + [Audit Cycle (date)]1, $concatenation:$[Shine Average], 19, false), LOOKUP("3. DF Maintenance" + " " + [Audit Cycle (date)]1, $concatenation:$[Shine Average], 19, false), LOOKUP("4. Quality" + " " + [Audit Cycle (date)]1, $concatenation:$[Shine Average], 19, false), LOOKUP("5. Storeroom-Logistics" + " " + [Audit Cycle (date)]1, $concatenation:$[Shine Average], 19, false), LOOKUP("6. Offices" + " " + [Audit Cycle (date)]1, $concatenation:$[Shine Average], 19, false)),

Shaine Greenwood

Luke,

Glad you got it figured out. Would you mind elaborating on the solution you found? (Just in case other community members could benefit from this.)

L_123

Sure not a problem. It was rather simple, and in hindsight I should have seen it right off. It's always like that though isn't it. 

I had lookups searching the first column for the information then reporting information 19 columns over meaning they were searching all of my data. I rearranged my information so the lookups had 4-6 total columns to look through instead of 19-21. The sheet still has its moments when it gets laggy, but for how complex and long the code is that's understandable.

In fact this sheet is probably useable as a template for most companies. Its formatted as a simple 3S summary for an audit.  I wouldn't mind sharing if there is a forum for submitting and viewing templates. It was a pain to create but should be easily editable for any company to complete 3S audits and do some basic calculations. The difficult part of this one was making it sortable while keeping the calculations correct as the inputs are not added in any meaningful way.  

(mine is for 4 facilities and 24 workgroups) The numbers don't mean anything right now as it is a proof of concept that hasn't been implemented yet.

Shaine Greenwood

Thank you! I edited the title and added the Templates tag to this post. 

Although we don't have a public customer template gallery, you're welcome to share your original sheet to anyone that requests with Viewer permissions.

They can save a copy of it to their own account and edit as needed.

L_123

Is that a future update? Not only would it be neat to see what other companies are doing with your program, if someone has created the same/similar sheet it could really reduce the amount of time spent making sheets in the first place. I'd be happy to contribute some of my more difficult to create sheets for other people to use for the chance to see more and better ways to use smartsheets.

Shaine Greenwood

We currently don't have plans to introduce a public template gallery but it is a good idea.

When you have a moment, submit this idea via the Product Enhancement Request form under Quick links on the right of the community site.

(I've already submitted internal feedback for this type of feature because I think it would be beneficial, but more requests can help it get more traction.)