-
Help with 6 month rolling summary metrics
I have a date column in a sheet and I am looking to count the number of dates that fall within the current, then current month +1, current month +2, so on a so forth. I think the tricky part is making sure the counts are accurate based on year as well. So since today is October 2, 2021 the rolling 6 months would be as…
-
VLOOKUP for cells that contain the search value but may not have an exact match?
I would like to use VLOOKUP to automatically fill cells with information from one sheet to another. On one sheet, each row of data represents one event. The other sheet (referenced by VLOOKUP) is for reserving resources for the event. Sheet A: Sheet B (referenced by VLOOKUP): The issue I'm having is that sometimes multiple…
-
Tracking Planned Start / Finish Dates vs. Actual / Current
I am in my first month with a company that has some existing templates to track schedules. The template sheet is set up so that when creating a new sheet from the template I plug in an end date and it calculates back to the start date of the first milestone. There is an additional column for "Current Finish", see below; As…
-
Duration of time formula
I have reviewed a lot of posts regarding time formulas and have discovered that there is no easy answer for these questions but I can't seem to get anything I am reading to work for me. So, here is my issue: We are trying to track the time it takes to work on a project. They are entering in a Start Time and an End Time. I…
-
How do I calculated Expected Completion %
Hi! My Smartsheet rep designed a formula for me but it's no longer working. I don't know what I changed to cause the error. I am trying to calculate if a project is ahead or behind schedule. Here is what I have that is returning the error #INVALISD DATA TYPE Assistance is very much appreciated.
-
IF COUNTIFS Formula for Multiple Status Circles
Hello, I currently have this formula: =IF(COUNTIFS(KPIs31:KPIs36, "Red") > 0, "Red", IF(COUNTIFS(KPIs31:KPIs36, "Yellow") > 0, "Yellow", "Green")) in the Parent row. This takes the underlying status circles (children) within the parent so if there's at least one Red, it results in Red in the parent cell. If there's at…
-
I am trying to count the number of rows in one column with reference to a value in another column
Hi All, I am trying to build a formula in 'summary sheet' data to refer two two columns. Count the number of rows in MCB Rational Column(if a value exists) only if the other column 'Count of current year' says 'true'. I am using formula - =COUNTIFS([MCB Rational]:[MCB Rational],@row, [Count for current year]:[Count for…
-
Clear Cell Workflow, not working with cross-sheet formula
Hello, I'm trying to clear the cells from a couple columns when a date is changed, but it is not working. The date has a formula that is currently pulling in data from another sheet. Could this be the reason why? I also tested a the clear cells workflow when a cell with a simple formula (not cross-sheet) is in it and it…
-
autopopulate cells in indented row
Hello, We track projects in indented rows from parent columns. I was wondering if there is the capability to automatically fill in certain columns in the indented rows from the parents. I know that you can add the information in manually using =parent(), but I was wondering if there was a way so taht every time I create an…
-
Sheet Summary Formula > COUNTIF for column using formula
Hello, I maintain a large document tracker. It has one "Pending" column that uses a massive formula (pasted below for reference) to search the tracker for missing information, and if it identifies any, the column is flagged. I'd like to create a summary formula to count the number of pending documents. I'm trying…
