-
VLOOKUP #NoMatch has me completely stumped
I have a vlookup formula in my sheet (Project Plan) that references another sheet (Phone List). Despite triple checking formulas, spelling, spaces, etc. I could not get the formula to return anything by #No Match. I copied and pasted the original sheet (Phone List) that's referenced into a new sheet (Copy of Phone List)…
-
How to duplicate the formula that sum across multiple sheets
We set up the sheet to report the daily sale volume of each project in the department. Each sheet is for one department. We create duplicate sheets for all departments in our company. Please see the screenshot. Now, we create a similar sheet to calculate the sum of the sale for each project for each day across all…
-
Parent/Child with Nested IF Statement using CONTAINS
I have a multi-select dropdown column in my worksheet called "Interdependencies." The sheet has parent rows that cover a main area with various child rows that cover discrete tasks. The dropdown options are: Learning Offers & Pathways B2C Partnerships Student Success Phase 1 Credentials + Employment Marketing & Demand Gen…
-
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…
-
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…