-
Formula to determine row number
Hello, I am wondering if there is a way to determine the row number of a specific row. I know this can be done in Excel with the row() function. This does not work in Smartsheet. The reason I am looking for it is because I have a formula that needs to apply to every row that checks the row above it, but that fails when it…
-
Matching multiple values from multiple columns in linked sheet to fill cell with content from third
I have Two sheets, one that acts as a "Rate card", it is broken down into Job title / Location / Day rate and the second is a budget sheet. For Example, Rate Card Column 1 / Column 2 / Column 3 Project Manager / UK / £10 Project Manager / US / £20 Developer / UK / £11 Developer / US / £22 In my Budget sheet I have a…
-
Formula for Status change based on dates/ blank cells/ TBD
Hello all, I really found this community helpful so far in learning how to use Smartsheets. I just wanted to provide a formula I created in case it is helpful for anyone else. I didn't find the exact info I needed when trying to make it, but I built this with the help of many discussion comments. This formula will…
-
Inconsistent Formula Function (seemingly, anyway)
I wrote a formula to calculate the number of days until a task is due. It works perfectly most of the time, but, on some rows, it doers not. Below are the actual copied and pasted formulas from the cells that say "OVER-DUE" and "-2, referenced from the screenshot: =IF([Task Total Hours]26 = 0, "", IF(Finish23 < TODAY(),…
-
New Formulas Q - HEXTODEC//DECTOHEX
Hello - What's the use case behind using the above formulas? Is it for working out HEX<>RGB color combinations?! Sean
-
Copy To Another Sheet
Please help! I was curious if there is a formula that would copy a row to another sheet, only if a certain column meets the criteria. I created two sheets that are exactly the same, but one is named Overtime and the other is Branch Visit. If the Branch Visit column states 'yes' on the Overtime sheet, can that entire row…
-
Formula to check Child Row checkbox if Parent row contains "ABC Dept" (filtering by Department)
I have a "Master" project resource sheet with great grandparent rows for "Current Projects" and "Future Projects", Then grandparent rows for each "Project", parent rows for department and child rows for the individual resources per department. I'm trying to create a filter to filter resources by department across…
-
Finding date in a child when other children are not always dates
I have a parent row that I am trying to use to summarise the Children rows. One column is a date called CloseDate. Rows are added every week, that create in effect a weekly record and the CloseDate is set to the word Active until the entry the week that it is closed and then a date is added. The row is not always at the…
-
Average formula with dates
Hi all, In our sheets we track the time we spend on a specific task on a certain day. So in our sheets we have for example: Due Date: 1-07-2019; Duration: 10m We can have multiple tasks for the same day. I am trying to come up with a formula in which I can calculate the average spend over the past 60 days on a client.…
-
How to use end date and percentage complete to automate RYG status
Hi everyone, Maybe my brain is struggling with IF and AND statements, but this is defeating me. I am trying to use the end date of a task compared to today's date and the percentage complete (great than or equal to 75%) to drive my status column of red, yellow, green balls. I think I need to find a way to combine the…