-
Creating unique participant IDs
Hi all, I am looking for a way to generate unique IDs for individuals filling out forms. We have a number of events for which we have made registration forms. When a person fills out a form, we would like to have the corresponding sheet automatically generate a unique ID for each person that is de-identified. Is there a…
-
SUMIF by month and by year
I have a metrics sheet with a cross sheet reference where I'm calculating a sum for each month with the below formula: For January =SUMIF({Refund Issue Date}, IFERROR(MONTH(@cell ), 0) = 1, {Refund Amount}) For February =SUMIF({Refund Issue Date}, IFERROR(MONTH(@cell ), 0) = 2, {Refund Amount}) and so on. How do I include…
-
How to use multi-select dropdown list as criteria for sumif/vlookup
Hey guys, thanks in advance for you help. I have three sheets that I am working with: a database (of sorts), a grid-form summary sheet, and sheet that contains attributed values. To simplify, I will only use a, b, c, and d as production point names and whole percentages as attributed values. The "database" contains unique…
-
Change the % Complete to 100% if Status is Completed
I am trying to get my project plan to change the % Complete to 100% if Status is Completed, does anyone know how to do this? I already have a formula around Task Health, and im trying to incorporate this into the change the % Complete to 100% if Status is Completed Anyone know how to accomplish this? =IF(Status@row =…
-
Conditional Formatting Combined with Dropdowns and Dates
Hello! I have a user tracking the progression of course development and is asking if there is a way to do some formatting on his sheet. The columns shown are set up as dropdowns (there are 8 total columns on this sheet). In the yellow row, in the cells where I've drawn a box, he is going to place due dates. Is it possible…
-
IfError, Average, Collect, and Round Up to nearest whole formula
I have this formula =IFERROR(AVG(COLLECT([Time to Complete]:[Time to Complete], [1. Assigned to]:[1. Assigned to], "W - Water")), "") How can I make the result round up to the nearest whole number?
-
Task totals across multiple plans & within a set timeframe
Hello! My formula skills are pretty basic so I feel like this might be a bit more than that. I am trying to calculate the number of tasks for four departments across multiple project plan sheets. I want to look at a timeframe scale of the last 3 months and the next 6 months. The four departments are: Editorial, Design,…
-
Help converting Excel formula to Smartsheet
I imported an Excel file into Smartsheet, and am stuck trying to convert this Excel formula into a functioning Smartsheet one. I know some of these functions are not available in Smartsheet. However, I've tried a lot of different formulas and am still having trouble. I've even tried using ChatGPT and it's proven to be…
-
Auto Numbering based on Impact and Date
This is a follow up to my previously posted question. https://community.smartsheet.com/discussion/137520/auto-numbering-question I am trying to number just the gap items, but all the high impact should come first in date order, and then medium impact in date order continuing. I was able to get it to start over the count…
-
Avoiding a 0 result
Hi! Context: We have tokens that are being spent at farmers markets. We call those "Incentive Redeemed." They originate from two different programs, funded by two different grants, and because they are identical when they are redeemed we do not know from where they originated. We call those "SNAP Incentive Issued" and…