-
Stacking Info from rows into Columns
So i use Smartsheet forms to Have my workers Log what they Completed on a Job Site as a Daily log. In a Day they Can do up to 8 tasks a single day and with the Form its set up to be just a 1 time Fill out to Track everything with out having to do the form 8 times in a row and just doing it once to cover everything. is…
-
#INVALID COLUMN VALUE error
Hello Community, I'm trying to combine two formulas but keep getting an error. The first formula sums children if it's a parent row or leaves the cell blank. The second look for the most recent data in a row (I have month in the columns from July to June). They work independently but when I combine them I keep getting an…
-
How can I log a timestamp of a cell change?
I want to add an automation rule to write a timestamp of when a cell value changes, I know I can use the "record a date" automation to get the date but is there any way I can get the exact timestamp similar to the "created at" field in form responses? I am also looking into a way to implement it using the API and there…
-
Trying to get a list of distinct values across multiple columns based on multiple criteria
Hi, I am trying to list all the distinct values for the Year 1 through Year 4 columns but only for the rows the WBS row value is equal to WBS Target value and skip any blank cells. I have a helper column to index up to 10 distinct values (Screen shot below) Any help is greatly appreciated!
-
Dynamic Date Calculations
We have different project types, and each project type has a different number of days is needed to complete a task. We have a sheet of about 30 rows that defines based on what project type, how many days it takes to complete the task. I want to make a date column to say "this is when this task is due" but I'm not sure if…
-
How to Sum a range if 2 criteria are met? (different range sizes)
Hi, I am trying to sum the total Inv. value in the below table but only for rows where the WBS target is contained in the WBS element AND where the Inv. Target Year appears in the range of Inv. years. In the below example, the the total would equal $500 because rows 1 and 2 contain 123 in the WBS Element column and the…
-
Formula Help: Find Value in 1st Row in Same Column, without using Absolute References
I'm trying to create a formula that gives me the value of the first row in the same column. [Find This Value]. There are a few obstacles: The formula is placed in the same column, BELOW value we're trying to find, in [Find This Value]13:[Find This Value]21. So it needs to be a cell formula and not a column formula And my…
-
Formula Help
Hello I am looking for assistance with a formula. What I need is '# of days for COC Completion' to equal 'Date COC was Finalized' - 'Date COC Determined' with a cavoite that if 'Was PCT hospitalized within 14 days of COC' = Yes, than instead of 'Date COC Determine' it needs to go off 'Date D/C from Hospital' Is that…
-
INDEX/MATCH showing #NOMATCH
I have 2 sheets: Source Sheet: KI Tasks Report Target Sheet: MLE Access Management All three columns in each sheet are text Formula in MLE Access ProjCode column: =INDEX({CE+RxE From KI_ProjectCode}, MATCH(ClientID@row, {CE+RxE From KI_ClientID}, 0)) Formula in Project Manager String column: =INDEX({CE+RxE From KI_PMName},…
-
If cell contains any of "apples", "oranges", "pears" in text string then collect
Hi, I have a column where I want to basically extract certain words from it without having to create multi helper columns. If I do a nested IF(OR(contains) statement or an IF(and nested statements it will look for conditions in order but I want to evaluate and return any of the certain words. I can do this with helper…