-
COUNTIF/SUMIF items with number of workdays between a date and a date range is 3
Hi! I would like to count a number of entries in a sheet if their due date is a certain number of work days before a date in another sheet. I tried to use the following formula: =COUNTIF('reference to a list of entries column in another sheet', NETDAYS('reference to due date column in another sheet', 'cell with a date in a…
-
Formula for returning value based on whether a date is between two dates
=INDEX(COLLECT({Payroll End date Range 6}, {Payroll End date Range 5}, Test@row >= @cell, {Payroll End date Range 7}, Test@row <= @cell), 1) Result: #Circular Reference or =IFERROR(INDEX(COLLECT({Payroll End date Range 6}, {Payroll End date Range 5}, ISDATE(@cell), {Payroll End date Range 7}, ISDATE(@cell), {Payroll End…
-
Looking for VLOOKUP formula to pull data from mulitple sheets
Hi all! I am attempting to use a VLOOKUP formula to pull data across multiple sheets. Since Smartsheet does not have the capability (yet, I hope!) to merge multiple form submissions onto one row, I am creating 2 other data collection sheets and need to pull that data to my master sheet. Each sheet has a unique reference…
-
Input/auto fill data from different rows
Hello, Please find the example photo I would like if there is a formula that can auto fill and input data in blank columns/same row once i type the phone number (in column phone) by calling or looking up for the data from other rows in same sheet (or from other data pase sheet) taht engaged with same phone number. For…
-
Converting Excel Formula to Smartsheets
Hello, Could anyone help me converting this formula into a formula acceptable on Smartsheets please? =IF(H13="","",EDATE(H13,INDEX($P$2:$P$10,MATCH(G13,$O$2:$O$10,0)))+INDEX($Q$2:$Q$10,MATCH(G13,$O$2:$O$10,0))) I keep getting the #UNPARSEABLE error. TIA
-
Fill in project number based on ancestor level
I want to auto fill the project_number between the the Purple and Red rows. So all the children of Purple Project have a Project_Number = 4 and all the Red Children have a Project_number of 7 Tried using something like this. So if the Project is checked return the Project_Number. This give me "UNPARSABLE"…
-
date range formula
Hi dear community, I need a formula to return the values "Process" and "At Risk" when a certain date is reached. I have a "Start date" column. I need to notify the assignee of the status for each task (all in separate columns). When we are 145-91 days from the start date, it should return "Process". When we are 90-0 days…
-
IF AND/OR plus a VLOOKUP
I'm having trouble getting a formula correct; I keep getting unparsable or circular reference. I'm wanting to add the name of our rep based on the status of two columns. If the CRM Portfolio is checked AND the Jurisdiction = Federal, I want to insert the name "Jason". If the CRM Portfolio is checked and the Jurisdiction is…
-
Simple Inventory Formula
Help, maybe I am overthinking this or going about it all wrong. I'm trying to create a SIMPLE inventory tracker, it does not include costs. I have a request form where users request an item and I want to subtract it from the existing stock on hand, be able to replenish inventory and adjust inventory if someone audits the…
-
Formula for Status Summary Report and Project Task Completion
Hi Smartsheet Team, I have the following charts. Left one, I retrieve the data from Status Summary Report. On the right side, I have the formula to calculate the Project task completion. However, you will see the complete % is different between these 2. My "Not Started" in Sheet Summary formula is =COUNTIFS([Count of…