-
Dates Less than 1 duration
Trying to get a formula to calculate less than day if duration is less than 1 whole day. Example - Start date is 7/30/2018 End date is 7/30/2018 Duration is .12d Formula Variable Date is 8/14/2018 If I want to run a formula to calculate the days from Variable date less end date - I get 15. I am looking for something more…
-
vlookup not updating
Has anyone had a problem with vlookup not updating? I had a situation this morning where vlookup was not refreshing the correct value when the search value changed. The way I found to fix it was to re-type the entire formula.
-
Zapier sends string, but Smartsheet processes as an integer
We use a unique number sequence by combining two integers for each row sent to Smartsheet. I worked with Zapier to verify that the integers are sent to Smartsheet as a string, not a number. What I am seeing is if the string is not long enough, Smartsheet converts the information to a number and our formulas do not…
-
formula referencing cell with formula
Hi folks. I'll do my best in describing my issue. Trying to create a formula that multiples 2 columns together however getting #INVALID OPERATION error. Does it have to do with a column that is actually a calculated formula? Column A: "Effective Time" is a calculated column based on this formula (shortened for this…
-
Maintaining Formulas When Sorting a Sheet
Hi, Is there a way to sort a sheet where the formulas do not change. I have a few formulas at the top of a sheet and whenever someone sorts through the sheet, my formulas change based on where it was moved to. My formula is a COUNTIFS and I want it to count what I am asking regardless of where in the sheet the row where…
-
Avg formula % complete based on a specific end date
Hello, I would like to know if it is possible to create a formula that calculate the average % complete for all tasks that are ending at a specific date (important milestone) in my schedule. The schedule I have have a lot of tasks going all the way to 2020 but I want to track only those that must be completed by Nov 2018.…
-
SUMIF formula is making me crazy.
Just when I think I am getting better with formulas I hit a wall with something that seems simple. I need to add 2 column sums together for the rows that have a RED ball Status. =SUMIF(Status:Status, "Red", [Repair Inv. Amount]:[Repair Inv. Amount] + [Diagnostic Inv. Amount]:[Diagnostic Inv. Amount]) Then I get an invalid…
-
Using a COUNTIFS formula to calculate # of rows in a set date range
I'm trying to calculate the number of tasks completed in the prior fiscal year, to be used as a dashboard metric. I've created a sheet that uses a COUNTIFS fomula to reference the relevant column in another sheet. Here is my formula, which returns a '0' result. =COUNTIFS({Completed Date},">=07/01/2017",{Completed…
-
Tips for Showing Count of Answers Completed on a Smartsheet?
If I have a section with three rows underneath my heading, I'm able to easily use a formula to show the count of tasks completed/responded to out of my total number: ="Introduction: " + COUNT(Response4:Response6) + " of " + COUNT(CHILDREN()) However, if I have a section with multiple sub-sections, I'm struggling to use a…
-
Display a counter in a bar graph
Hi community, I'm making a bar graph which shows information in %, so each bar is the percentage of the information I want to show, but this '%' comes from a scalar number which I also want to display. Is there a way to do this? I attach an example.