-
Rank Top 20 Clients By Value of Won Opportunities
We have a sheet we use to track opportunities. I'm trying to figure out a formula to create a list of top clients by the value of won opportunities in a metrics sheet so that I can add the list to a dashboard. Above is an example of the data. The list of clients changes so the formula needs to by dynamic. I want to show…
-
Stumped on combining AND/IF/OR formula
I need a formula where if Innovate Audit is Yes, or Innovate Audit Upgrade is Yes, but Pull Print is No, then the the # of devices requiring in printer agent/40 +1 Here is what I came up with so far... =IF(OR([Innovate Audit]@row = "Yes", [Innovate Audit Included in Upgrade?]@row = "Yes"), IF(AND([HP Advance Pull…
-
Counting Concurrent Jobs
Each row in this spreadsheet is a "Job". I'm trying to count (project the count by month) concurrent "Active" jobs (rows) based on a start and finish date of the job. I created two columns (per 12 months) to set the criteria for each months calculation. If the monthly criteria falls between the start and finish dates of…
-
COUNTIFS referencing date ranges specified in Sheet Summary
I'm trying to count the number of cells (dates) that fall between two dates that are selected in sheet summary. In excel I've been able to make this work =COUNTIFS($C$7:$C$47,">="&$H$1,C7:C47,"<="&$H$2) H1 and H2 being the relative date range I'm wanting to count between. I just don't know how to convert this into…
-
Status Formula
Hi all, I am struggling to create a formula that works and wonder if you can help me…. Duration Equal to or Less than 0, Gray. Duration Greater than 0 but “Email Sent” & “Return Old Device” unchecked, Yellow. Duration Greater than 20 but “Email Sent & “Return Old Device” unchecked, Red. Duration Greater than 0 and “Email…
-
Would Like to Copy a value from one location to another weekly
Hello! I have 2 sheets SHEETA and SHEETB. SHEETA has a ton of data in it and I want to take a value from one cell inside or the sheet summary it and copy it weekly to another sheet. I know I can copy rows but that is not an option for me. Is there another method I could utilize to automate this?
-
Information from multiple columns into one
I have a sheet that includes department and sub-department data, which is collected from a form. The sub departments all populate into different columns, so i have sub department columns (A, B, C, and D). For each row, there is only information in ONE of those columns because people only have one subdept. I want to create…
-
Formulas based off dollar amounts
Looking for a formula that says if column a has a dollar amount that is greater than $3000.00, display 1, if column b has a dollar amount that is greater than $3000.00, display 2.
-
Row number
Hi Thrill Seekers, I am seeking a way to access the row number of a row in a sheet. I would be very happy if this came in the form of a SmartSheet function (let's call it Row) so that I could create formulas like "=Row()" for the current row number or "=Row()+2" for the number of the row two rows below the current row.…
-
Progress Status / Health Automation
Good morning everyone, I'm new to Smartsheets and have been really enjoying the functionalities and automations. I'm trying to set up an automated Health / Progress status and keep getting a "#UNPARSEABLE" error. I've looked through my code and can't figure it out.... Can someone take a look and help me see what I'm…