-
Auto fill a second column based on a drop down selection using a form
I understand I can likely do this with a very, very long VLOOKUP or IF/THEN formula in every cell, but I'm dealing with over a hundred items and that will be unwieldy. Situation: I would like to have a form where someone selects one option from a dropdown of ~100 items. That value then populates into a sheet. (This seems…
-
Help with COUNTIFS function with external references where first variable has multiple choices
Hi, I have a sheet with ranges identified as SUBJECTM and FLAGM. On my summary page, I have a list of all variables, with the column identified as Metric. In the main sheet, the subject field is a series of checkboxes with the ability to select multiple options (think : maths, english, french, art). I want to count the…
-
Date functions in IF Statement
Hi, I'm trying to get the previous month and previous year via a formula. Here is the formula I am trying to use: =IF(MONTH(TODAY()) = "2", MONTH(TODAY()) - 1 + "/" + YEAR(TODAY()) - 1, "false") It works when I remove the -1 from the YEAR(TODAY()) - 1 part, but when I add the -1 back in I get #INVALID OPERATION. Any ideas?…
-
Nested IF Formula Troubles
We are trying to use a nested IF formula to use two different sets of criteria to return two different results. I feel we are close because we get an Incorrect Argument error, rather than the dreaded Unparsable, but I am not seeing what we are missing. We also tried adding a "" as a return if False value but that didn't…
-
Formula to Find combined cell total if criteria is met.
Hello, Please Help!! I am working on a metrics sheet from an onboarding sheet. I am trying to find the total of FTE for each department. I have a column called "FTE" which has various numbers in over 200 cells (e.g. 1, 0.78, 0.25 etc.) and i have a column called "Department" which has 5 service areas within the…
-
Including a checkbox column in formula returns "INCORRECT ARGUMENT SET"
Hi all, I am trying to make a formula that Calculates the hours spent on tasks with certain conditions. I have 2 arguments in my formula that are currently causing it to return this output. Here is the formula: =SUMIFS({Custom Engineering Requests: Days To Complete}, {Custom Engineering Requests: Drawing Category},…
-
Add Count Unique function
Please add a COUNTUNIQUE function (or COUNTU for short). It's a pretty self explanatory and similar to other COUNT functions. My intent is to have an automatic Project ID number that is based on the unique project title and I can have multiple entries of the same project title without the ID changing. There is a work…
-
Formula to return Monday from a week a date was on
Hello, I'm looking to put together a formula that will pull the Monday a date was on so I can then pull things that were 1 week ago, 2 weeks ago, etc. to build out a report for weekly hours done by members of a team. I've tried some other methods to try and do this but they broke on the year change because Smartsheet just…
-
Need Help With Colored Health Status Formula
Hello, I have a symbol column that automatically will assign a color (currently either red, yellow, or green) based on a due date in a column called "NEED ON SITE DATE". The formula looks at the "Fab Status % Complete (Needs Real Numbers)" column and will assign it a green dot if the percent complete is 100%(In this case…
-
Index Collect or Lookup Formula Help
Good Afternoon All, Currently having trouble with an index collect formula between 2 SmartSheets. On Sheet A there are 50 Unique ID's that correspond to 50 Creative Names on Sheet A. On Sheet B I am trying to bring those 50 unique Id's over by matching back to the 50 Creative Names on Sheet A. So my formula is as such:…
