-
How can I exclude "child rows" when executing VLOOKUP?
I am currently trying to update the VLOOKUP formula to exclude child rows. I setup a reference sheet to run the formula, but the only way for it to run properly is to add the child rows. If any child row value is changed, it shows as #NO MATCH, however, I would just like to appear blank. I am currently running the current…
-
How to Step Rounding Percentage
Below is a very basic formula actual/workset a to get the %achieved. You can see that the two calculations are off one should be 33%, the other 20% What is the best way to show the actual value, not the rounded value? Thanks!
-
Index Collect and Multiple Output Values
Hi All, I have multiple things going on and am hoping I can find one formula to clean things up for my team. Basically, I have the below list as a helper sheet. Then, i have a form that our customers can complete. They can select from the list that is on the left of the example below and they can select multiple options…
-
VLOOKUP with concatenate
My project has a "master project" sheet and then dozens of individual project sheets. On the mast project spreadsheet, people will enter 3 letter customer mnemonics [Organization] and select from a drop down list in [Project}. The drop down list could be a single or multiple from that list. The primary column is a…
-
Pulling just the date from a cell
Hi! I think my question is pretty simple. On a sheet, I have two columns. One is "Primary", and it's blank. The other is "Created" - a date/time column that auto-populates from a form. I want the primary cell to only pull the date from the Created column. I've tried a few different formulas with no luck. Anyone have any…
-
How to change formulas when a filter is applied?
I want to create a master sheet that connects to a report for each customer in the data. In this master sheet I have a column for "Average process completion time". In the master sheet, I want it to display the average completion time for all customers, which it already does. However, when I filter a customer in their…
-
Avg/Collect formula to enter zero
Hello, I have this formula which works as I'd like but what I'm missing is if the answer is zero I want the formula to give me 0 as a response, instead I'm getting #Divide by zero. Any suggestions of what I'm missing? =AVG(COLLECT({days complete}, {Sourcing Owner}, [Contract Manager]@row, {Completed Requests by Month Range…
-
Formula Help Needed - Return "50" if date is within a range AND another field is not blank
Hello! I've been working on this formula for hours now and could use some help. I have a column called "Program Date" and a column called "TR". If Program Date is between 12/1/24 and 4/30/25 AND TR is not blank - then I need it to return the value "50" in my "Cost" column. This is where I am at so far: =IF(AND([Program…
-
COUNTIF specific date
Please can you help with a formula to count how many times on another sheet (B Shift List) the date in the column is within August.
-
Calculating the Average of Descendant Rows
Hi there, I am having some trouble calculating the average of the "RYG Average" column for the child rows in my Smartsheet. I have a helper column called "Parent Row" that has a checkbox for any rows that are considered parent rows. I am wanting to calculate the RYG Average for the child rows under each parent row. Here is…
