-
If or Index or Match....not sure what to use
I have a Metrics sheet and a Source sheet. The Source sheet has 2 columns, Area and Status. On the Metrics sheet, for each Area, I want to display that associated Status. This is the Metrics Sheet (The % Complete will be pulled from another Source sheet): This is the Source sheet: I tried: Any ideas?
-
Is there a revised formula that will allow me to show more accurate training dates?
I have a formula in a training matrix that compares dates of when a document revises and when the training was carried out for employees. =IF([Revision Date]@row > [employee]93, true, false) However, this returns a red font date if the revision date is greater than the training date, even if the employee was trained prior…
-
Adding months (stipulated in one cell) to a date (stipulated in another cell)
I have used this formula: =DATE(IF(MOD(VALUE(MONTH([Date Approved]@row)) + MTE@row, 12) = 0, -1, 0) + YEAR([Date Approved]@row) + (MONTH([Date Approved]@row) + MTE@row - MOD(MONTH([Date Approved]@row) + MTE@row, 12)) / 12, IF(MOD(VALUE(MONTH([Date Approved]@row) + MTE@row), 12) = 0, 12, MOD(MONTH([Date Approved]@row) +…
-
Puzzle and Possible Formula Solution Inquiry?
I need to create a formula to pull information from a Smartsheet to another with two different columns. For example: Year Criteria Geography 2023 Yellow North 2022 Orange East Let's say there are 50 entries that meet these criteria. I need a formula that will pull all 2023 Yellow North data to another Smartsheet. Then,…
-
check box if name matches, drop-down result is x, and date is within range
Hi formula wizards! This is what I want to do: My sheet has a list of customer names and a column with a checkbox for each month of the year. This will be put on a dashboard to show when visits were made to each customer. I did think of adding all these columns to the contact tracker and hiding them, that's also an option…
-
CTC hours used by the task and role month-wise and visualize it
Hi, I am trying to figure out how to calculate the CTC hours month wise used by the task and the role seperate. Is there any way to find it using formulas or widgets? Appreciate your help
-
Adding specified months to a date in another column
Hi Looking for some help. I've tried several formulas (Mainly suggested by Chat GPT) but can't seem to get anything to do the following: Take 'End Date' column cell Add however many Months are specified in 'Defects Period' column cell (This is currently a set dropdown) Return date in the 'End of Defects Date' Column cell…
-
#nested criteria multiple columns
I have a timesheet that I'm using formulas to calculate the different categories of time using three columns - Date, OOH (1.5x) and OOH (2x). The first formula below is the original: =SUMIFS([OOH (1.5x)]:[OOH (1.5x)], Date:Date, AND(IFERROR(MONTH(@cell), 0) = 8)) + SUMIFS([OOH (2x)]:[OOH (2x)], Date:Date,…
-
I have ranges and the second range has more than one value in each cell. With a single value - the
Application - SmartSheet - need someone who knows Smartsheet formulas/functions. I need help with a formula/function - using countifs - I have ranges and the second range has more than one value in each cell. With a single value - the formula works - with more than one, it doesn't Range1 = Office Column Range2 = Status…
-
Index Match Question
Hello, I am having some trouble getting a date from 2 different sheets to populate in a 3rd. As a standalone formula, these work just fine. Is there a way to combine so that I am indexing 2 ranges and returning the requested value if there is a match? =INDEX({Training Requests / Tracking Range 2}, MATCH([Location ID]@row,…
