If I understand correctly, then this should work...
=COUNTIFS(CHILDREN(), OR(@cell = "Listo", @cell = "No aplica")) / COUNTIFS(CHILDREN(), OR(@cell = "", @cell<> ""))
My apologies, you are totally right! You can use an expression to input the 1 in that helper checkbox column instead of adjusting your source sheet.
You are also correct in how this would be set up: in the data uploader workflow in the Mapping window, in the Input Column/Expression left side of the window, choose the option "New Expression" from the bottom of the drop-down list to Map to the column DL?
Then have this expression simply be =1
This will check the box in Smartsheet for the rows that were updated.
Formulas in the sheet itself will be a bit different, and I'm happy to help you with this.
Instead of this, =IF([Account Number]@row=NOTBLANK,"Yes",""), try this:
=IF([Account Number]@row <> "", "Yes", "")
<> means Not and "" means Blank so it's essentially the exact same as what you had written above. However, based on your description it sounds like there will always be an account number, but what you're looking for is if there was a match with the Data Uploader and an updated Balance, is that correct?
If so, you can adjust the mapping to include checking the box, as above, then use my previously suggested formula:
=IF(AND(ISBLANK([Account Balance]@row), [DL?]@row = 1), "Complete, Waiting for Download", IF([DL?]@row = 1, "Complete", "Not Complete"))
This will then tell you if there was a match but the balance is blank, or otherwise, there was a match and it's complete, or finally if there was no checkbox then that row is not complete.
I have re-produced your issue here and hopefully provided you with an answer
The function I used for the How many in progress row was:
=COUNTIFS([% Complete]:[% Complete], >0, [% Complete]:[% Complete], <1)
Hope this helps :)
Debbie Sawyer Consultant & Training Manager
Try using @cell references...
=COUNTIF([% Complete]:[% Complete], AND(@cell <> 0 , @cell <> 1))
It is a decent requirement but I don't know of a way of setting cell b's value based on the condition meeting true in cell a. I could do it in MS Access back in the day - but I don't think you can do this in Smartsheet. The cell that is being set (i.e. the checkbox clearing) needs to be the cell that contains the formula and if someone puts a tick in the box prior to the need for it to be cleared, the tick will overwrite the formula to take it out again.
You can format a cell based on another cells value, but not change the value of a cell based on another cells value.
As mentioned above this could be achieved in an API or using Zapier potentially...
Maybe you could request this from Smartsheet as a product enhancement?
If you wanted to drop this into your [90 Days Grace Expires] column instead of yet another date column, you would use the formula the is in the [90 Days Grace Expires] column (without the beginning equals sign) in the formula above wherever you see [90 Days Grace Expires]@row.
=IFERROR(DATE(YEAR([email protected] + [Grace Days Allowed]@row), MONTH([email protected] + [Grace Days Allowed]@row) + 1, 1), DATE(YEAR([email protected] + [Grace Days Allowed]@row) + 1, 1, 1)) - 1
If you plan on using this sheet beyond this COVID-19 mess, you can even automate the [Grace Days Allowed] column to automatically populate 90 if it falls between two certain dates and 0 if it falls outside of those dates. You can hardcode those dates in a "helper" column or (if you have access to it) in two Sheet Summary Fields.
Then you would only need to update those two reference dates and the rest of your sheet will automatically determine if the date qualifies for the 90 days or not and will automatically run your calculations for you.
If you are able to automate that part, then you could even modify the formula to reside in the [Event Due] column and hide all of the calculation columns so that everything happens on the "back-end". This could help keep the sheet clean and provides the ability to maintain it even outside of the COVID-19 Grace Period.
Yes, if you add at least one placeholder to the subject or message, it will send one email per request up to approximately 7, I think.
Would that work?
I hope that helps!
Be safe and have a fantastic weekend!
Workflow Consultant / CEO @ WORK BOLD
✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
Unfortunately, it's not possible at the moment, but it's an excellent idea!
Please submit an Enhancement Request when you have a moment
You have to enable the Wrap feature. (see below)
Did that work?
Be safe and have a fantastic week!
Or you could just use
in the Target and Actual cells.
[email protected] - [email protected]
in the [+/-] cell.
There is no need to specify to ignore blank fields as they are counted as zero anyway and won't change your final numbers.