using a filter selection in dashboard to filter out account

Hilbert Kok

Hi I have created a dashboard where i want the user to be able to filter out month and the account.

I have created a sheet that is taking the data out of 14 sheets and populates the first row based on the selection made in the dashboard. i have done that using a form that has 2 fields (account and month).

The data is then used in a countifs to find the data in the sheets, so far so good.

I have set the form to add the submitted data at the top. All formula fields are set as column formula.

every time you submit the form a new line is added to the sheet and as such also the data is shown in the dashboard. I don't want that, I only want the new selection to be visible. So I want the old row to be remove when the new row is entered in the sheet.

I tried to do this with a workflow but it is very unstable. Is there an easy way to remove the "old" row when new data is submitted?

could data schuttle help here?

Thx Hilbert

Genevieve P.

Hi @Hilbert Kok

The way I would do this is to set up a helper column in my sheet that checks a box if the current row is the newest row (using a MAX formula looking at a System Created Date column).

Here's how I'd write the formula:

=IF([Created Date]@row = MAX([Created Date]:[Created Date]), 1, 0)

Then I would use a Report to only show the row that has this checkbox (using a filter) and display the Report on the Dashboard instead of the sheet. Would this work for you?

Cheers!

Genevieve

Hilbert Kok

Hi Genevieve, Thanks for your response.

i have done something simular. I created an account and month selection field in a grid, linked that to a report and created a dynamic view to make it robust for the user. I have created a IF-THEN formula to search the value in the account field and compare that with a column and if present show that account, then I used a filter to that column to show only non blank fields. It works great.

The thing that I don't understand why the dynamic view shows me lower rows in the view which creates confusion at the user, because they can also use the rows to select the account or the month, and because my formula doesn't look at other rows, the user doesn't see any data. I have now added a clear explanation how to use it, but I don't want to have the possibility that user accidently choose the wrong row for selection.

Any thoughts on that challange?

Genevieve P.

Hi @Hilbert Kok

I have to admit I'm not sure I'm quite clear about this set-up. It sounds like you have it working so I would stick with this for now! If you don't like blank rows showing in Dynamic View perhaps you could make the widget shorter, to cut off some of the blank areas?

It looks like your account has access to Pro Desk sessions; it may help to show someone over screen-share what it is you have set up and ask for advice on reaching your end goal. You can book a 30-min coaching session here: https://www.smartsheet.com/pro-desk

Cheers,

Genevieve