-
How do I count by year based off a condition?
Hello, I'm trying to count how many dates in each state per year (snapshot below). I've tried this formula but it's not counting correctly: =COUNTIFS({State/country}, @cell = "NSW", {Date 6th Day was worked:}, AND(IFERROR(YEAR(@cell), 0) - 2021)) What do I need to change?
-
Pull a row from another sheet.
Hello, I was wondering is someone could help me with a formula. I would like to have a range of rows from one sheet to replicate in another. e.g Sheet 1 Row 3 - Dataset1 Row 4 - Dataset2 Row 5 - Dataset3 Row 6 - Blank Row 7 - Blank Sheet 2 Row 10 = Dataset1 from Sheet 1 Row 11 = Dataset2 from Sheet 1 Row 12 = Dataset3 from…
-
Scheduled Health Rollup using "Red Yellow Green" formula
Question. I am using this formula for my individual task health: =IF([% Complete]@row = 1, "Green", IF([Start Date]@row > TODAY(), "Green", IF(AND([End Date]@row <= TODAY(), [Start Date]@row < TODAY()), "Red", IF(NOT([Start Date]@row > TODAY(+7)), "Yellow", IF([Start Date]@row <= TODAY(), "Green"))))) Works great! I would…
-
Is there a way/function to indicate lack of presence for a cell-linked cell?
I have a sheet where a certain column is nearly all cell-linked cells. A few cells in that column, however, were manually-entered. Is there a formula, or a conditional formatting, that I could apply to indicate that the particular cell is not cell-linked so that users know that it was manually entered? Further, is there a…
-
SUMIF stopped working
This formula was working in my sheet (summary field) and it just stopped. I don't know what I could have changed to make this stop working. I am now getting an invalid data type error. It's a simple formula! If the year is 2022, sum up the charges column. My Year column is calculated, YEAR([Date Signed]), is that why? I…
-
Updating/Refreshing my file from a different website?
I have a file that I need to use updated fuel pricing at all times. The website my excel file is linked to updates once a week, usually on Tuesday. Need: How can I have this sheet refreshed from the website (EIA.gov) onto my Smartsheet file? Nice to have: I would like the data point I use on the sheet to be rounded to 2…
-
Contact Support Page not working? Having cross-sheet formula issue
Two issues here: #1 - Anyone else having issues with the Contact Support Page? I keep filling out the form and it's going nowhere? No field errors either? It's been 2 days now with no luck so hopefully can get some help here. #2 - Cross sheet formula Issue i'm experiencing. I have 3 sheets - one is a data source sheet, the…
-
Why do my Index Match fields not show the results on Reports?
I have some reports from a sheet that populates a few fields from an index/match formula. On my report, the data doesn't come through. It shows #INVALID REF even though the data is correct on the Smartsheet. Also, it seems like the report isn't updating when those fields on the Smartsheet are updated. I have some other…
-
Parent Row Status Formula Help
In the Project Roll-up Template, I'd like to add a "Not Applicable" as a status selection for rows that won't be applicable. If a single children row is "Not Applicable", I don't want the parent row status to change, if that makes sense. Currently if a single child row is "Not Applicable", the parent row status goes to "In…
-
Automating Time Spent
Hello, I've been reading through various articles, threads, and Google searches looking to see if there was a solution to calcuate how long it takes to complete a task. Currently, I have a sheet user inputing a new row and the first portion of data, lets just say columns A:D. Then, they save and work in another system.…