-
VLOOKUP returning #INVALID REF
I am receiving the #INVALID REF error in smartsheet and I am not sure how to correct it. =VLOOKUP([PART NUMBER]1, [PN]1:[PART DESCRIPTION]1800, {2,3,4,5}, false) Below is the excel sheet formula that does work but does not flow over to the smartsheet. =VLOOKUP($Q6,$A4:$D25014,{2,3,4,5},FALSE) If you look at the excel…
-
Is there a formula in smartsheet that would create a 2 way link between cells?
Hello, I have multiple sheets that I would like to link all into one Master Sheet. I want the cells to have a two linking methods between each other. If a cell changes in the Master Sheet, I want it to reflect to the other sheets and if the other sheet decides to change the cells, it would reflect back to the Master Sheet.…
-
Formulas not carrying down to new rows
I have a sheet with formulas in most of the columns that should be carrying down to newly added rows but after several months of it working correctly, they no longer carry down. I have checked for blank rows and made sure the formulas are correct in each column and present in every row but after working for awhile, they…
-
Formula to calculate date
Hello, I am trying to work backwards but I just don't get it. My event date is my constraint, I want some tasks to be completed x days prior to the event and Smartsheet to calculate the start date of the tasks automatically. For example, send agenda to attendees 2 months prior to the event. I've tried playing with the…
-
how to handle duplicate data from the source sheet when using vlookup?
Hi, I have a work schedule sheet (source/reference data) and shift schedule of employees is being logged there. Employees shift schedule can be night shift and day shift per employee. Now, I have another sheet (Control log sheet) where I am looking up the work schedule of each employee in a daily basis. When I am using the…
-
Cell Reference Within Same Sheet Not Working
Hi I want to reference column "Start Date" in the column "End Date". I want the formula in "End Date" to be =Start Date15+5 (add 5 days to start date). However, for some reason it is not working. I am using this in the Gantt tracker. Do you have any idea how can I make it work? I have noticed that I am not able to…
-
Duplicate Check Formula
Hello There, I have created a column for duplicate check, It will flag the as red if both conditions are true Condition 1 : CPR ID Duplicate Condition 2 : Amount Duplicate Formula which is giving me error: =IF(AND(COUNTIF([CPR ID#]:[CPR ID#], [CPR ID#]1) > 1, IF(COUNTIF(Amount:Amount, Amount1) > 1, 1))) I think i am not…
-
Countifs for a date range
Hello Team I need to calculate count of countries within the date range of request received . I am using below formula but its throwing invalid operation error, could u please correct me where I am making mistake. Current Formula: =COUNTIFS({Table 1 Range 1}, {Table 1 Range 30}, >=[Date Received From]32, {Table 1 Range…
-
When added to a project, automatically add project to team member project page
I am a newbie with this, so hopefully I make sense. What we are trying to do is when we add someone to a project, that they will have the project added automatically to their personal project page where they can keep track of their hours spent on the project. Ideally we would like to see the total hours for each project…
-
Issues combining 2 formulas
I have the following columns: Expected Budget, Director Approval, VP Approval, Approved Projects. The conditions being, if Expected Budget is less than $10000, only director approval required, if more than $10000, both director and VP approval is required. On the Approved Projects, I would like to show if the project is…