Best Of
Re: How to count the number of photo shoots, not completed, by brand? COMPLEX???
Hi @Shawn_K2 , Have you attempted to create a report and specifying the filters that you listed? If not I would try that route first to see if that meets your need.
Within reports, I would recommend looking into Filters, Grouping, and Summarize. These are all features that you can find solid help for within the community.
If you make a little progress, but need some additional help, share a little more about what you've tried. I'm happy to help, but it's hard to describe without knowing what you've been trying.
I hope this is gets you going in the right direction. Be well!
Re: Can Entries be Added in to the Calendar App from the Calendar itself? see screenshots
This looks strange, can you try another browser and also check once from the below help document you hvae followed all the steps correctly.
Calendar App | Smartsheet Learning Center
Warm Regards
Re: Smartsheet Comments tags person in Slack
Hi @Humashankar - I can add them as a contact in Smartsheet but when I make a comment, it will not allow me to @mention them. I just found out that this cannot be done.
There currently isn't a way to have the Smartsheet notification sent to Slack automatically @mention a specific person within the information sent. You will want to start a thread in Slack under the specific form response and @mention the user manually in a comment.
Re: In access request alerts please include a second button to share the workspace
YESSS! I had to remove all the admins from my workspaces with this change that made it easier for sysadmins but hosed solution engineers. 🤬🤦♀️
Re: How to Add Categories in Personal Budget Management Template
Hi @jakeloy1
Template Sets tend to have instructions embedded in the set, usually in the form of Column Descriptions. In your case, if you hover over the "i" on the Category column of the Intake sheet, it should tell you what to do! 🙂
To update the dropdown values, double click on the column title "Category" and change the values in the square box:
To update the metric sheet, go to the Budget Set Up sheet and change out the highlighted yellow values to match your current values:
If you have more than 10, no problem!
Create a new row below the "Budget Category 10" row and make sure it was automatically indented it so it's under the parent "Categories". You can test to see if it's correctly indented by clicking the - next to the word "Categories" and ensure it collapses with the other rows.
Then create a new row to match under the "Percent Allocation (%)" parent section, linking in the value by using a simple = sign and clicking on your new Category name:
You'll notice that the other formulas above this cell have a $ sign in front of the reference, which is best practice when you want to ensure it stays "stuck" on that column. (See: Create a cell or column reference in a formula)
Use Drag-Fill to drag down the formula that shows the % in the Value column:
You'll need to do the same thing for every section that has Categories listed: add a new row, bring in the Category Value from the top section of the sheet, drag-fill formulas where they appear. You'll need to use a bit of detective work to figure out which of the formulas you can simply drag, and which ones you may need to replicate by finding the cells that the other formulas are referencing and building yours out to be similar.
EXAMPLES:
For the Budget section, you can drag-fill the entire formula section down:
And it will update to reference the correct cells.
But the Expenses section references rows below it that won't exist until you add in your new category. Double clicking on a formula will highlight the cells it is referencing so you can re-create what it's doing by referencing your newly created rows.
Does that make sense? If you get stuck with a particular formula let us know!
Cheers,
Genevieve
Re: I'm getting incorrect calculations for this simple formula.
The formula you are using seems to be missing some brackets and possibly has a syntax error. For Smartsheet, it's important to ensure proper use of parentheses for functions and to reference cells correctly.
The correct formula for calculating "Net Balance" considering that you want to subtract 'Deposit Amount', 'Credit Amount', and 'Discount Amount' from 'Gross Amount' for each row should look like this:
=[Gross Amount]@row - [Deposit Amount]@row - [Credit Amount]@row - [Discount Amount]@row
If you want to ensure that the values are treated as numbers, you can wrap each cell reference with the VALUE()
function, but it is not usually necessary unless the cell is formatted as text.
For summing up values, you can simply add the values as shown in the formula above. The SUM()
function is used when you want to add up a range of cells or multiple ranges, and it's not necessary when you are subtracting individual cell values from one another.
Re: レポートでリアルタイムの値が表示されない
@Ruka さん こんにちは。
ほぼ同じ質問についてのディスカッションがこちらにあります。
https://community.smartsheet.com/discussion/73023/auto-refresh-reports
内容を要約すると、Smartsheet のダッシュボードでレポートが自動的に更新されないというものです。
問題: ダッシュボードに入力されたレポートが自動更新されない。 ダッシュボードに変更を反映させるには、シートとレポートを手動で更新する必要があります。
考えられる原因: 社員管理者の@Genevieve P. さんが指摘しているように、TODAY() 関数は、シートを開いて保存しない限り、現在の日付を認識しません (システム時間に依存しているため)。 @Genevieve P. さんがヘルプのリンクを貼っていますが、 @Ruka さんのケースは、「下のアクションをとっても、TODAY 関数を使用する数式は現在の日付に更新されません。」に当たるのではないでしょうか?
https://help.smartsheet.com/ja/function/today
次の場合、シート上の TODAY 関数のすべてのインスタンスが更新されます。
- シートを開いて保存したとき
- フォームの送信からシートに新しい行を追加したとき
- 更新または承認リクエストからシートに変更を加えたとき (アラートの詳細はこちらをご覧ください)
- レポートからシートを更新したとき (レポートの詳細はこちらをご覧ください)
- リンクされたセルがシートを更新したとき (セル リンクの詳細はこちらをご覧ください)
- シート間の数式で参照されているシートを開いたとき (シート間の数式の詳細はこちらをご覧ください)
- 行を移動またはコピーするワークフローからシートに新しい行を追加したとき (このようなワークフローの詳細はこちらをご覧ください)
- セルを変更、ユーザーを割り当て、日付を記録などのワークフローを使用して、セルを更新したとき (シート変更ワークフロー アクションすべての詳細はこちらをご覧ください)
以下のアクションをとっても、TODAY 関数を使用する数式は現在の日付に更新されません。
- シート内の関数を参照するレポートを開く。
- シート内の情報を表示するダッシュボードを開く。
https://help.smartsheet.com/ja/function/today
解決策
上のTODAY 関数が更新される場合の「ワークフローを使用して、セルを更新したとき」を利用した解決策が2つ紹介されています。
日付の自動記録: このワークフローは毎日実行され、ヘルパー列を現在の日付で更新し、TODAY関数が更新されるようにします。
ロック/ロック解除オートメーション: シートのロックとアンロックに別々のオートメーションを設定すると、保存がトリガーされるというものです。
最初の方法は「日付記録のアクション」を使うものでTODAY関数そのものに依存するので確実な方法と考えられます。2つ目のロック、ロック解除の自動化は、新たなヘルパー列の追加が不要、というアイデアです。
1日に1回でなく頻繁によりリアルタイムに近づけるには、@Jason Albrecht さんが紹介しているように「1日の異なる時間にトリガーされるワークフローをいくつか用意」するのが良いでしょう。
念の為: ダッシュボードの更新設定が頻繁に更新されるように設定されていることを確認してください (数分以内)。 😅
Re: Hello fellow Healthcare professionals
Hello Amanda,
nice to meet you.
I am working the past 4 years with Smartsheet but not in project management... we use smartsheet as scheduling system for training sessions, installation and as approval platform for several procedures.
It is a pleasure to share knowledge any time.
Re: Column Formula for a future date, 3 months before annual start date
Hi @PEvansLC,
we can't directly iterate over years or dynamically generate column values based on another column's multi-year span directly within a single formula. However, you can achieve your goal by setting up a series of columns that calculate these future dates based on the initial start date. For each additional year, you would have a new column calculating the next review date. Let's outline a strategy for the first few years, assuming you might have up to a 5-year term as you mentioned.
General Strategy
Determine Review Dates: For each year, calculate the review date by subtracting 3 months from the start date. If the month of the start date is Jan, Feb, or Mar, you'll need to adjust the year backward appropriately since subtracting 3 months would land in the previous year.
Handle Jan/Feb/Mar Specifically: For months Jan, Feb, and Mar, subtract an additional year from the year part of the date since subtracting 3 months from these months will go into the previous year.
Active Status Check: Incorporate a check for the [Status] column to ensure the calculation only applies to rows with an "Active" status.
Example Formulas for Yearly Review Dates
Year 1 Review Date Column:
=IF([Status]@row = "Active", IF(MONTH([Start Date]@row) <= 3, DATE(YEAR([Start Date]@row) - 1, MONTH([Start Date]@row) + 9, DAY([Start Date]@row)), DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) - 3, DAY([Start Date]@row))), "")
Year 2 Review Date Column:
=IF([Status]@row = "Active", IF(MONTH([Start Date]@row) <= 3, DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 9, DAY([Start Date]@row)), DATE(YEAR([Start Date]@row) + 1, MONTH([Start Date]@row) - 3, DAY([Start Date]@row))), "")
- For each subsequent year (3rd year, 4th year, etc.), you would create a new column and adjust the formula accordingly by adding to the year calculation.
- The formulas check for the
[Status]
column being "Active" and adjust dates for Jan, Feb, and Mar by either subtracting from or adding to the year, depending on the context. - Ensure that for each formula, you adjust the calculation based on the specific year you're targeting.
Re: Column Formula for a future date, 3 months before annual start date
@Humashankar It's probably not that simple. [Start Date] is for a contract date. The [End Date] is the last date of the approved term. Term can be for one year, or up to 5 years. Regardless if term is more than one year, an annual review must be performed at 3 months before that [End Date] Month/Day for current year. Meaning, [Start Date] = 11/2/21, need to perform that review on 8/2/22, 8/2/23, 8/2/24, and so on. Hoping to create columns to represent these individual year ranges.
So, If [Status] = "Active", and date range for the [FY Review Date] Column = 12/1/23-11/30/24, the [FY Review Date]@row should calculate to be the same Day in the [Start Date]@row, AND 3 months less than the [Start Date] month, while ensuring that the Year is within the specified range for the [FY Review Date] column.] If [Status} <> Active, show blank. It get tricky when dealing with Jan/Feb/Mar, because it then needs to be the preceding year.
I've tried to just do 9 months ahead of [Start Date], but cannot get that to calculate more than one year past the start date. Any insight is appreciated.