-
Counting the number of unique values in a column
I feel like this should be simple, but I can't work it out! My smartsheet has 753 rows, but there are only 479 unique values in the column I'm looking at (which I discovered in Excel). How can I count this using a Smartsheet formula? I only want a number. No criteria. Help! Thanks in advance.
-
VLOOKUP - reference sheet, based on two criteria
Hello, Is it possible to use a VLOOKUP formula to pull data from a reference sheet based on conditions in the original sheet? I have two things I am trying to have automatically calculated in my original sheet: # of Team Members (based on the Total Enrollment and Classification columns on this sheet that tell me what…
-
Removing Illegal Characters using nested SUBSTITUTE failing
Hello, I am trying to use the following formula to remove illegal characters from a string (situated in column "name"), but it fails as unparsable. Can anyone tell me why? Is there a limit I've gone over? I note that the formula is trying to use the brackets (parentheses in USA) even though they are enclosed in quotation…
-
COUNTIFS - using a date
Hi - looking for help with what I think would be an easy formula. What I want to do is create the number of "Complete" tasks I have that are due BEFORE 3/31/2021. Can anyone help with my formula and point out an error? I get UNPARSEABLE as a result. =COUNTIFS(Status2:Status571, "Complete", [Due…
-
Have 1 cell populate another
I have SHEET A and SHEET B; on each, there is a column LAST_NAME. Is there any way to have SHEET B be populated when SHEET A is filled in? In other words, if someone enters "SMITH" in the LAST_NAME column of SHEET A, I would love to see it automatically populate the LAST_NAME cell in SHEET B. Please advise and thanks in…
-
Formula to update value when newer dates are added
I have a master data sheet where all of my formulas and data live. This sheet feeds into our financial dashboard and I'd like the formulas to be working efficiently so I don't have to go in and constantly update them. I want to create a formula in my master data sheet that searches for the most recent date in my AR…
-
VLOOKUP with contacts - contact name appears but not the associated info
I have a sheet where I am using VLOOKUP in a contact column to pull in the associated project manager based on a date. The VLOOKUP is your standard formula: =VLOOKUP($[Target Date]$5, {Launch Date}, 3, false) The reference table is at column 3 is also a contact column and the project managers in this column have their…
-
Change status automatically when selecting % done
Hello I have searched through the Help centre (center!) and I can't find the formula (if one exists....) that will automatically select the appropriate value in the % done when one of 6 choices from Status is chosen. So when I select Not Started, I would like 0% to appear in the % Done, Started=25%, In progress=50%,…
-
Formula Not Updating
I am using the following formula cannot get it to pull the data. It pulled it once but now it is coming back as 0 and will not update. =COUNTIFS({Medxcel Fire Invoices Range 7}, 1, {Medxcel Fire Invoices Range 6}, 1)
-
Index/Match Function
Hey all - Needing some help with using this formula, I can't seem to understand/figure it out when trying to look up values. Here is what I have - (sheet 1) I'm trying to look up the values under 'Function' and bring back the result from a different sheet (shown below) - (sheet 2) So in this case, the value 'N' should come…