-
Using multi-select dropdown list as criteria for Index/Match Lookup with SUMIFS
Hi all — Looking for some guidance on the below… I have a sheet named "Tracker" with a dropdown column of job levels (named "Target Audience") and another column that I am wanting to sum the job levels from whatever is selected for that row ("Total Target"). The totals for each job level are pulling from as a cross sheet…
-
Numerical value established by a formula not seen as a number
Hello I have used the mid formula to break down a date and indicate the number of the month only in a field. =MID([Start Date]@row, 2, 1). This is all working fine. I want to run an automation where if the value of this field is less than 5 do x. However it is not seeing the value as a number and is thus returning zero…
-
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?