-
INDEX Match throwing incorrect answers
I am trying to use a unique identifier by column, to reference another table that has that unique identifier in it and provide it's adjacent value from another column, by row. This is the formula I'm using and it's giving me values in that list but the wrong value: =INDEX({Hours}, MATCH([Combined Work Description]@row,…
-
Creating a column from multiple columns, with conditions
I have created a sheet which is populated via a form for the auditing of stock. Each audit consists of 20 items. I'd like to create a separate sheet that only lists the items that have discrepancies. Sheet looks something like this… Would like the Discrepancy Sheet to look like this: So, the audit number, item number and…
-
How do I create a fraction to represent the amount done
I am trying to create a fraction to show the #closed items vs the number of total items. I have already counted them so for example in column 1 it says 5 and in column 2 it says 25. I want to see in the next column a fraction that represents it so in this example it would say 5/25.
-
Extract a MM/DD/YY Date from M/D/YYYY HH:MM
I'm looking for some formula help to convert a date format. The date column I'm referencing is coming in from another source, and is formatted as M/D/YYYY HH:MM - here's some example data from the sheet: I need to compare these dates to a user-set date (column = Prior Run Date) in my sheet, which is a Date column type…
-
how to add 6 months in a date ?
Hi, I have a date column and I want to generate a new date by adding 6 months to the date. I have used this formula- =DATE(YEAR([Go Live Date in V2]@row), MONTH([Go Live Date in V2]@row) + 1, DAY([Go Live Date in V2]@row)) but its only working within the year but if the date comes under next year it gave me an error so can…
-
Column Formula - Populate data from 1 cell down each row.
Hi SMEs and friends, BLOT: I need Client Name (field value in [Project Task]2) and Client ID (field value in [Project Task]3) to populate for each row in [Client Name Helper] and [Client ID Helper] columns. This will be used for reporting and dash build out at a later time for a few hundred clients. I don't know much but I…
-
Expected % Complete not match automated % Complete Parent Rows Formula
I am attempting to do a Expected % complete and I cannot determine how to align my parent rows calculation to match the automated % complete calculation within Smartsheet. Currently all children row formulas are the following: =ROUND(IF([Estimated Start Date]@row < TODAY(), MIN((TODAY() - [Estimated Start Date]@row) /…
-
Overall Project Health Formula
Hiya folks, We want one field to show Overall Project Health. Ideally, we would figure out how to average the colors and/or tasks past due to return RYBG for the overall projects (about ~230) so that we can report it on a Dashboard. Any ideas on formula would be so appreciated. Circular reference error formula in OPH row:…
-
Average Based on 2 Separate Criteria
I'm trying to create a formula that will average the age of an event if it meets two separate criteria: 1. The event occurred in that month (date of notification) 2. The event status is "Complete" The age of each event is already calculated for each event in a separate column. I'm getting a syntax error with the formula…
-
Creating Expiration dates based on products
I want to create a formula that will auto-generate expiration dates for individual products for an entire column. The sheet has a created date and column containing a dropdown list of products. The products have different expiration dates. Ideally I would like to format the expiration date column to have the product…
