-
How To create a revenue summary formula
How can I create a formula where by I have rows containing different projects, their value and the dates they will start and end and create a summary to show in what months will generate what value (assuming the value is divided equally over however many months the project spans) i.e. Row 1 is Project X with a value of…
-
Display formula result in form
Hi, I have a column formula that grab a link from reference sheet and I need this to be displayed before submitting the form. Example, column A is a drop down of items, each of these items can be downloaded from a shared link. So in column B I added a formula to grab the link from referenced table. However in the form I…
-
Building a sheet to best populate dashboards
Do you have any training material you can share on “best practices” for building a project plan/timeline to populate Dashboards correctly (e.g. metrics and tables). Examples of things I want to do: Present the overall project health as red, yellow, green; sum of late tasks; sum of tasks in progress and on-track; sum of…
-
SUMIFS between date range formula not working
Hi All, I am trying to sum the total overtime hours cost for various roles on a project and I am breaking this down into weeks, so I need my formula to look at a range between 2 dates. My formula works correctly for some roles but not others. The OT hours information is collected via a smartsheet form which, amongst other…
-
Link Reference Sheet Dropdown
Is it possible to reference a list on a separate sheet for the user to select as they input data into rows?For example; activity type, client name? I have created a time sheet and would like users to reference the data in another sheet. Thank you, Rachel
-
Auto-number Column and MAX COLLECT with Criteria
Hey friends! I want to combine the two below operations into one column: I'd like to return the highest value in Auto-numbered column when the criteria is met from another column. ~Return the highest/largest auto-number, from the "Row ID | Auto-Numbered" column, matched with each ID# in the "⚠Mac ID#" column. Then, when…
-
Sheet Summary Field Formula to Summarize Expenses in a Column by Year
I have a sheet that includes a column of net costs with a Year column. I want to create a sheet summary field with a formula that calculates the total net costs for a specific year (e.g., 2023). I tried the following =SUMIFS(Revenue_Column, Year_Column, Year_Value) but kept getting an error, and I don't think it's…
-
IF statement using AND to generate colored symbol
Hello! Can somebody please help with the following formula? I am trying to generate the colored symbols (Green,Yellow,Red) based on the percentage of tasks complete along with the go live date. I am able to get a single If statement to generate the symbol but am failing at the AND one. Below is the formula that I'm using…
-
Formula to count the number of cells that contain 3 criteria including name within a list
I have tried a number of formulas and each time run into an error regarding the last item. I want to count a row if a name appears within a list in a cell with a designated date and location within that row. =COUNTIFS({Class Attendance Range - Date}, [DATE], {Class Attendance Range -Location}, [LOCATION], {Class Attendance…
-
How to I calculate percentage progress based on status?
Hello Smartsheet Community, I appreciate your support on calculating "% of complete" based on "Status" value. I have some predefined values for "Status" column and need to connect the "Status" to "% of Complete". For example: When Status="Backlog" --> % Complete=0% When Status="In Evaluation" --> % Complete = 25% When…