-
How do I refer to specific child row.
Hi, I need help to do some calculations on daily basis (columns 01 to 31) at each parent level . For e.g In below screenshot If I want to calculate difference between (a child rows) "Total Vulnerability count" - "Total New Vulnerability Count" every day using generic formula so that I can apply at each parent level. what…
-
How do I count multiple types in a column?
This is my current formula that works: =COUNTIFS({STATUS}, "Completed", {AUDIENCE CATEGORY}, HAS(@cell, "All Employees"), {QUARTER}, HAS(@cell, "Q1'23")) But now, I need the formula to count multiple quarters (see the last part of the equation). I need it to pull Q1'23, Q2'23, Q3'23, Q4'23. How do I add those on? ... Thank…
-
How to extract a number from a cell that has text+numbers using vlookup function
Hi all, I'm pulling Line numbers from another production schedule in the format of: Line01, Line02, etc. and I'm trying to perform a VLOOKUP FUNCTION using the WO number to pull the value from another sheet. As you can see below I'm able to do so easily in the example. The problem I'm having is that we recently introduce…
-
Whys is TODAY(-90) returning values for future dates
I am trying to sum the # of PTO hours an employee has used in the last 90 days for some reason my formula =SUMIFS({Missed Hours}, {TM}, $Name@row, {Type}, PTO$1, {Date}, >=TODAY(-90), {Cancel}, 0) is tallying all of the time off requested even for future dates. I would expect the value returned to be 56 however it is…
-
Cross-sheet formula?
Hello Smartsheets Team, I'm curious if it's possible to enter a value on one sheet, and have it go through a formula on another sheet, then return the end result back to my main sheet. Basically I have a sheet with multiple vendors and associated formulas for each vendor to get a final price (there's many details for the…
-
Calculating Percentages in Smartsheet based on the parent value
I have two columns in my sheet. One is for the data and the other is for the percentage. In the data column, I have 5 rows with numbers 1, 2, 3, 4, and 5. The total of these numbers is 15, which is the parent value. I want to use a single formula in the percentage column to calculate the percentage of each of these 5 rows…
-
Continue Count When Row Is Moved To New Sheet
Hi, I'm attempting to create an auto-number row that takes into account what year it is for a ticket submission form sheet. To do this I realize I must use a formula instead of the autonumber column. However, I want the auto number to continue once a row is moved to a new sheet (The row gets moved and deleted from the…
-
Not working
I have a helper column named "location ~ 2023 - 01 january" and it combined a location and the FY reporting month if a site has reported. what i created was an IF(MATCH(location ~ 2023 - 01 january, matches the location one on the other sheet, 0), "Yes", "No"). meaning that if the locations matched then it would read yes…
-
What's the purpose of ISBLANK or NOT(ISBLANK
Pretty simple and quick question. Why would I use an ISBLANK formula, instead of just adding ="" in a logical expression? Or why would I use NOT(ISBLANK instead of <>"" in a logical expression? Maybe because it's easier to interpret if ISBLANK or NOT(ISBLANK is used?
-
Subtracting values based on checkbox column
I am using a formula to subtract a 30 minute lunch period from the total minutes lapsed based on whether the lunch field has a checkbox or not. Within the if statement, if the start time hour is lesser than the end time hour the formula works as expected and 30 minutes are subtracted from the total minutes lapsed (criteria…
