-
Autopopulating contacts using vlookup
Hi, I have a sheet with several formulas, including a =vlookup on a text cell and =Join (). All these formulae autopopulate when a new row is added manually of via a form submission. However, I also have a contact column which, while successfully pulling the contact information into the cell using a =vlookup function, is…
-
Locking Cell - with Conditions
Is there any way to lock/restrict a cell that asks users to choose a date? For example, I want users to be able to choose a date earlier than what is "listed" in the cell but be unable to choose a date after the date that is listed. If the cell reads 9/1/2019, the user should be able to change the date to 8/30/19 but NOT…
-
Sorting and conserving formula
When I sort rows, the formulas are not maintained: = SUMIF($[column_A]$7:$[column_A]237, "Received", [Column_B]$7:[Column_B]237)) gets transformed into this: = SUMIF($[column_A]$57:$[column_A]237, "Received", [Column_B]$57:[Column_B]237)) the $ has no effect. and I cannot use the [column_A]:[column_A] syntax as it will…
-
Sumifs Date Range w Cross sheet formulas
Im close on a sumifs formula, but for some reason i cant quite get it. trying to count installations on a give date from a given list of properties Here's what i have. it's returning a unparceable response =SUMIFS({7900 Park Central Install QTY}:{7900 Park Central Install QTY}, {7900 Park Central Install Date}:{7900 Park…
-
Sum of Children - not working
Hello All, I'm trying to get the sum of children. There are 4 children rows, with multiple rows beneath that. See image. In this case, the top row with cell value 0 would have a 2 because it's summing the purple 0 and purple 2. Purple 0 and Purple 2 are counting the cells below (children) that have checkboxes. I'm using…
-
Using CHILDREN(cell address) as a range in SUMIFS
I am trying to find the total of Task Total Hours only for rows with a tier level of 2 and where % complete = 1. I am writing this in a summary cell in a mini-dashboard that requires me to write the formula in the % Complete column. I am trying to use CHILDREN(cell address) as a range. What I have read leads me to believe…
-
Sum of quantities on a given date
I have a date column for installs. I also have a column where we enter a quantity. I am trying measure installs on each day of the month. We want to be able to look at a particular date and see how many installs we have on this date. We have about 120 properties Im thinking a roll up sheet with each of the properties on a…
-
Formula to change text
Hello, How do you generate a formula to change the text within a cell. I want to have different text for different due dates such as if the due date is within 5 days, I want the "status" cell to say immediate, and if it is within 2 months, I want the status cell to say "short term", if it is within 4 months, I want the…
-
CountIf and Exclude Status
Hello! I am trying to count the number of requests types and exclude certain statuses. The current formula is: =COUNTIF({Request Tracker Range 4 - Request Type}, Category13). I want to display the current number of Client or Internal requests and omit Completed or Declined. Any suggestions are appreciated!
-
Formula that averages a column
HI Smartsheet community, I need a formula that averages one column (# of jobs) if a checkbox is checked. I think I need to use AVERAGEIF and ISBOOLEAN, but I can't get the syntax right. Help! -Bridget Sloane