IF formula on Dashboard

aleksandra.marszalek91136

I would like to show on dashboard most important numbers including the latest License Expiration Date.

I already created "help column" in the same smartsheet as data, where I use formula:

=IF([Expire data license]@row = MIN(COLLECT([Expire data license]:[Expire data license]; [Expire data license]:[Expire data license]; >=TODAY())); 1; 0)

so I have overview on which rows (Applications) expires soon, but

I would like to show those on Dashboard + Application name (from separat column) + that specific expiration date

 

any ideas ?

Paul Newcome

Have you tried building a report off of the help column?

aleksandra.marszalek91136

I thought about it, but in that smartsheet with data I have 160 rows, so in the report I will get all of them, but I want only the specific one with latest Expiration Date, which will be maybe 5

Paul Newcome

You can include in your report criteria that the helper column is flagged or checked however you have the column formatted.

Paul Newcome

Another thing to keep in mind is that the TODAY() function will not update until the sheet is activated. If the sheet isn't activated for two weeks, then the data in your report will be two weeks old.

aleksandra.marszalek91136

what you mean "acivated" ? not in use ? 

aleksandra.marszalek91136

Can I do those "help columns" in other smartsheet with reference to that specific one with data ? I tried but alsways I got an error

Andrée Starå

Hi Aleksandra,

Can you describe your process in more detail and maybe share 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)

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

By activating the sheet, I mean the sheet needs to essentially be opened before the TODAY() function will update to today's date. Otherwise it will remain on the last date that the sheet was opened.

aleksandra.marszalek91136

I would like to have the latest license Expiration Date on Dashboard.

many information including license expiration date is placed on Application Budget Smartsheet:

Columns from which information can be pick it up do Dashboard:

Application name - column nr 5

License Expiration Date - column nr 18

 

so now I thought I would be a good start to first define which applications has the lates expiration date, so I created a "help column" with checkbox with formula:

=IF([Expire data license]@row = MIN(COLLECT([Expire data license]:[Expire data license]; [Expire data license]:[Expire data license]; >=TODAY())); 1; 0)

Next Expiration date - column nr 19

 

so now I dont really know how to take the chosen data from Next Expiration date and Application name on Dashboard ??

Paul Newcome

You can build a report. Reference the sheet and in the "What?" criteria, you can select the checkbox column and use "is checked".

 

You can then click on the "Columns" button in the report builder and add the additional columns you want to display.

 

You can then either use a Report widget or a URL widget with a published link to the report on the dashboard to display it. The option of these two that you choose is going to depend on your specific case.

Andrée Starå

I saw that Paul answered already!

Let me know if I can help with anything else!

Best,

Andrée