-
MAX Formula Help
I am working on a max formula that I can't seem to figure out how to set up the criteria. Background: I have a sheet (Intake Sheet) that is gathering project health data on a weekly basis via update request. Each week after it is collected, every row gets copied to another sheet (Historical Intake) so always have a…
-
Calculate the # of months between 2 dates, with 1 date being static
Hi all, I am trying to insert a formula that will calculate the number of months (overlapping years) between two dates; 1) Requested Start Date, which is a data column on my sheet, and 2) 12/1/22 as a static date in every calculation. So far I have been able to create a basic formula that works, however, I had to create…
-
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…
