Sheets set up

Christine Menke

Hi Experts I have a question.

I'm wondering, what's the best way to set up my sheets.

I have a Tracker A which gets input from customers during the year (Q1-Q4) The Tracker has several columns first 10 are general columns which are the same for each quarter and than I have 20 columns to be entered by the customer during each quarter. During the seasons there are rows added.

In addition the Tracker will be use for several customers, so each customer will have his own Tracker A, B, C, D.... and the dashboard will than summarize the data for all customers(Tracker A,B,C,....) I plan to have a metric page for each Quarter.

For each Quarter I would like to have a dashboard. I need to have a stop of data entering after each Quarter. If a row is added at Q2 it should not be visible at Q1 dashboard.

I'm wondering what is the easiest way to set it up. Create a Tracker which includes already all Quarters and hide the quarters which are not needed at this point of time or to create a Tracker only for each Quarter?

What do you think is the best set up and creation of Templates to reduce workload for the future.

Thanks!

Sheet setup.png

BullandKhmer

Is the timing of the additional entries per quarter predictable, i.e at set times. If so I would suggest:

Intial entry by Forms

Additional info added to rows via automated update request.

Auto Date column on each row

Set up reports for dashboard with a filter based on the autodate row.

And FYI, forget about seperate sheets for seperate clients - Just set all client facing objects to current user view.

BullandKhmer

Yeah, this is a single sheet solution.

Christine Menke

Thanks @BullandKhmer for your feedback.

good ideas, but the single sheet solution might not work as I have confidential data, means customer 1 is not allowed to see customer 2 data.

And FYI, forget about seperate sheets for seperate clients - Just set all client facing objects to current user view.

If the client would remove the filter he/she could see all clients, right? I was also thinking about Dynamic view, but I miss the filter function here as well.

If I use forms the customer needs to enter every single row, right? If I use a sheet he could copy paste e.g. 5 rows if the content is the same.

But the dashboard filter by row creation date might work. Thanks

Andrée Starå

Hi @Christine Menke

I hope you're well and safe!

To add to Adam's excellent advice/answer.

Have you explored using one of the Premium Apps, Dynamic View, or WorkApps (Included in Enterprise and higher)?

Another option that I've developed is something I call, Dynamic View light, but if that's an option depends on if they need to make changes in many columns. It's pretty advanced, but in short, it uses cross-sheet formulas and INDEX/MATCH and a specific structure and method, so it's only possible to show data that should be available.

More info: 

https://www.smartsheet.com/product/dynamic-view

https://www.smartsheet.com/platform/workapps

Would any of those options 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 or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

BullandKhmer

Just dont let them have access to the sheet, reports & dashboards with current user view settings.

Christine Menke

Thanks @BullandKhmer,

with the current user view it will not work, because I share the sheet with a group of customer e.g. Customer 1 and in this group they allocate the people to the project and put in more details.

Thanks @Andrée Starå,

I tried Dynamic view 1,5 years ago, but I miss some function here. But I will check if there are some updates which could work now.

I will have a closer look to work apps. Would it be possible here to share a filter and let them see only the rows under the pre-selected filter? Or are they able to change the filter to see all the content?

Christine Menke

@BullandKhmer & @Andrée Starå ,

thanks for your great ideas.

I checked the Dynamic View and the Work app.

both are great tools, but not perfect for my setup.

Dynamic View here you could give access to only one customer which is great but it's still not perfect as you need to update every single cell and can't do the update in the sheet ( e.g. copy one cell to the complete column if needed, it will take too long for the customers.

Work app: I like the access management, but if I share a filter e.g. by customer1, than he could still remove the filter und would see the other customer input.

Confidentially is quite important.

I guess I need is a shared filter function which you cannot adjust and the possibility to enter in the sheet directly.

Is this matching to the Dynamic View light you mentioned @Andrée StaråThanks

Andrée Starå

@Christine Menke

Happy to help!

WorkApps can be set to the current users, and they won't see anything else.

Yes, my solution, Dynamic View light, could probably work as well, but with it, they can't edit the cells from the main sheet, but they can add information to other columns that sync back to the main sheet.

Make sense?

Would any of those options work?

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

Christine Menke

Thanks @Andrée Starå ,

WorkApps can be set to the current users, and they won't see anything else.

I tried the current user filter with my colleagues. So I give the user edit rights for the sheet in the work app as they need to update the cells. I shared the default Filter "current user" with him.

He sees only his projects, that is fine. But he is able to change the filter from current user to off and than he see all the details, I would like to hide. Did I miss anything in my set up?

Thanks for your help as always!

Andrée Starå

@Christine Menke

You're more than welcome!

It only works with a Report set to the current user.

Would that still work?

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