-
Ancestors / Parents and concatenation of all level to make a string / map of the heirarchy
Hello smartSheet world, Can we concatenate all parents in a Column/field values together? Example, If we have 5 levels; 123 ABC abc 987 i As descriptive text in each field (e.g. a Name of a WBS Level in a Project.) Using the above to make the below adding a " " in between each (Could be any character) "123 ABC abc 987 i"…
-
Join Collect Formula with Contains?
Trying to figure out the following formula but am having trouble getting it to work: =JOIN(COLLECT({GHL Maintenance Range 1}, {GHL Maintenance Range 2}, CONTAINS([User Email]@row), ", ")) Basically trying to a join collect which is simple enough, but I want it to count if the user email is Contained in a cell where there…
-
VLOOKUP Doesn't Appear To Work With Auto-Number Columns/Cells
I have a main sheet containing a list of projects with the first column containing the ID of the project which is configured as an auto-number column. I am working on setting up another sheet to track projects tasks and want to be able to link a task to its associated project in the project sheet. There are select columns…
-
I need to collect the average of a column by referencing name in another column
I have columns in a sheet per the screen below. The Team Member is input through a form submission and can potentially be input many times. I am looking for a formula to collect the average score from all rows where one name appears without having to say ="Scooby Doo".
-
SUMIFS Issue
I have a fairly straightforward formula that i cant get to work, driving me crazy! I typically prefer Sumifs over Sumif but currently I am summing a kpi column of numbers if two additional columns (both Text/dropdown (single Select format) report a specific set of words. what am i missing!?! Thanks in advance. =SUMIFS([KPI…
-
Formula Auditing/Evaluation
How do you audit or evaluate a formula in smartsheet? In MS Excel, you can audit/evaluate a formula to see the calculations for each step of the formula. I can't see a way to do this in smartsheet. I have a simple formula that should be giving me a specific value, but it's not, and I can't figure out why since I can't…
-
Sumifs to count color symbols
Hello, I have a spreadsheet where I am tracking initiatives by team for each month and there is a color assigned to that initiative. For each month I assign these colors. I have a total of 12 columns. Green for on track, Yellow for at-risk, Red for breached, and Gray for not started. I really wished I could add one more…
-
Count if
Hi I'm wondering if someone could assist me. I writing a count if for a column and it isn't counting the item if there is more than one answer in a row. Example would be =Countif([Case Type]:[Case Type], ="Birth Certificate". If you see below where Birth Certificate and SSI are in the same row, my formula is not counting…
-
Formula not updating
I have a very simple formula. =[Unit Price]@row * Units@row. It is suppose to take the unit price cell in the row and multiply it by the units cell in that same row. 9 times out of 10 it works fine, but every once in a while it wont update. This is causing us invoicing issues within out company as we have to go back and…
-
Count only child rows that have "Complete" in 3 columns
Hi I am trying to count the number of child rows that have "Complete" in 3 different columns. Here is my current formula: =COUNTIFS((CHILDREN)1, [HS Germ]:[HS Germ], "Complete", [HS Pave]:[HS Pave], "Complete", [Classroom]:[Classroom], "Complete") I keep getting "UNPARSEABLE". Any suggestions?