-
Vlookup from another sheet where data is not in left to right order
Hello! I am trying to pull data into a Smartsheet from another VERY large Smartsheet. The data I am looking up is to the right of the data I want to populate via a VLOOKUP, which is why I believe my formula isn't working. Example: On my new sheet, my search value is located in column 3, which would be the Purchase Order…
-
Metric for number of incomplete parent tasks on dashboard
Hi everyone, I'm trying to figure out a way to add a metric to my dashboard that will count the number of incomplete parent tasks across all of my sheets. I've gotten as far as creating a report that filters down to just the parent tasks that haven't been marked to "complete", but haven't been able to figure out a function…
-
Automating Quarters
Hello, I need to know if there is a way that I can populate the Quarter automatically based on the system automated "Created (Date)" field in a sheet? If the Created Date is between 1/1/2021 and 3/31/2021, I want the next column in the row to populate "1st - 2021" If the Created Date is between 4/1/2021 and 06/30/2021, I…
-
Formula for showing subtraction/sum compared to total
Hi there, I'm working on testing quality assurance of products sent to my company. For each row in my Smartsheet represents one product I am testing. Each row requires testing on 160 samples. In my reports I want to show the "Result Summary" column where if there were no nonconformities, then it would show up as "160/160".…
-
Formulas being converted to values
Hi - I have two sheets where I have created formulas that return a percentage for child tasks based on hours estimated and date ranges that ultimately feed into the resource management view. The formula works as expected (initially), but when I close the workplan and reopen, it will (at some point), convert the formula to…
-
Do I use the count or countif formula
I am trying to create a formula that pulls a count based on data in two columns. Example: I am trying to set up a sheet summary with counts for specific information. Column 1 is a text/number column and is titled "AB Comments" the second column is also a text/number column is titled "status" is is possible for me to tell…
-
If Children Contain then display cell on that row in Parent Cell
Have children() that vary in range, children rows can vary from 1 to ~30. For each child that contains "Action" in column [Issue Category] display the cell Notes contents into parent row Notes cell. Currently using static formula. Issue is every time a row is inserted, I have to manually add to the formula. Current formula…
-
SUMIFS and Weekday to pull totals for report weekly
Hi all, I am trying to figure out a way to 1). sum data coming in between Monday and Saturday) in the sheet summary, and 2). use this to create a report with totals based on our workweek (Monday-Saturday). I created a helper column to utilize the "WEEKDAY" function ("Date Lab Results RCVD Helper"). Getting this to sum the…
-
I am trying to look across 4 columns to pick out if anyone Denied a Access Request
With this formula below I can see if each of the 4 people Approved something, but how can I add a nested section that would also mark a [Approval Check] field "Someone Denied The Request" if any one of the 4 approvers mark the approval as "Declined" =IF(AND([Bob Approval]@row = "Approved", [Jack Approval]@row = "Approved",…
-
How to estimate hours per Task using % allocation, Assigned to and Duration Columns?
Hi Everyone, I hope you all are doing alright. We need to estimate how many hours will problaby be spent on each task. We already use %allocation, Assigned to and Duration columns in our cronograms. We can't use the suggested formula "%allocation * Duration * 8" because: 1) Multiple employees can be assigned on the same…
