-
Alternative for Vlookup to search for a column rather than a row
I'm trying to simplify a formula that works, but will become too long for the cell. The Current Formula is =IF([Equipment Max#]@row = "", "", IF([Equipment Required]@row = "FOB", IF(COUNTIFS({EDBFOB}, >[Equipment Max#]@row, {EDBDate}, >=Start@row, {EDBDate}, <=[Last Date]@row) > 0, "Error"), IF([Equipment Required]@row =…
-
Is it possilble to use a calculated row reference in a formula?
I have a table (2x12) of data (sales $) for each month (one row per month) in the Current Year(CY) and Previous Year (PY) columns. I'm trying to calculate the YTD sales (by month) of CY and PY based on today's date using the function, Today(). I know I can use a formula like PY-YTD = SUM(PY1:PY4) to sum the first four rows…
-
Set date in month column when another column used to set the date when task was completed
Hi Smartsheet gurus I have a need where I want to auto update a column corresponding to its month where in another column is used to set the date a task was completed - the need is to sustain the past months date in the month it was completed column - example - see attacehd sheet
-
How to create a formula that indicates duplicates
Can someone give me guidance or instructions on the best way to do a formula that would indicate duplicate reqs. on our onboarding tracker? This is what I'm looking to do "new checkbox column and write the formula to check the box whenever there was a duplicate and then added a conditional formatting rule to highlight the…
-
Resource View doesn't have the correct Allocation %
I am creating a Resource View that my team can use to track work allocation. It works, but there are several places where the data is not accurate - the allocation % on the Resource View does not match the allocation % on the project plan. For instance, in one Smartsheet, we have a task for 1 hour, which comes out to be…
-
Need help pulling distinct count from another sheet
Hi. I am creating a rollup sheet using two separate directories imported onto separate sheets. Looking for Unique NPI and Unique locations. The following formula works for the first entry, but is returning 1 for the next 3. =COUNT(DISTINCT(COLLECT({DE Location ID}, {DE Benefit Name}, "vision", {DE Client Name},…
-
Is there a way to have a date change in autofill within a formula?
I am looking to shift the way my data is displayed so I can place it in a dashboard. To do this, I'd like to shift the information within to do a specific date and return that. My formula works fine, but I have to change the date for every single line. Wondering if there's a shortcut or something that I could do to avoid…
-
Checkbox cross referencing person on another sheet
I am looking for a formula to cross check another sheet to check a box on my cumulative sheet. Example, someone completes a required training on EM# 1 sheet then on the EM# 2 sheet I want that persons name to be checked from a list of other individuals. This is the formula I used, =IF({Emergency Preparedness Employee…
-
Move Rows Automation - I can't find the sheet I want to move all of my rows to?!
Hi All, I have a Smartsheet which contains client quotations. I want to build an automation that will move expired quotes out to a 'Cold Leads' Sheet so the main sheet just contains confirmed quotes. I want to target the automation so a checkbox is selected by the user, when that checkbox is checked, it will move the row…
-
Smartsheet Formulas for Risk Assessment
Hi All, Has anyone used Smartsheet to monitor risk? I am curious how this was designed and how effective it was. I am looking for something that could populate a dashboard. Could I populate a simple dashboard showing some levels of risk in areas that looks something like this? A. INHERENT RISK SCORE: Risk Impact: Value…