-
Select Most Recent Date In List
Hello! I need a formula to pull the most recent date in a list, but I keep getting unparsable and invalid errors. Could someone please help me create a formula to put in the highlighted cell? Please note there are empty dates (like the top row). This sheet is driven by a form, so new dates may be added out of order - in…
-
Way to calculate parent row % Complete in other columns (like % Complete) for SPI.
We have a Planned Value column that is based on Start and End dates and a SPI column that is based on Planned Value and % Complete. Our Planned Value column is used to show what the task % Complete SHOULD be for the project where the % Complete is what the ACTUAL % Complete for the project is. The SPI uses %…
-
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…