-
Setting up VLOOKUP correctly
Hello, I have a source sheet that has a unique ID, and I use that as a reference to generate a number of references in a second sheet for extra details. I am using VLOOKUP to bring about 10 columns from the source sheet into the second sheet. The typical formula looks like this: =VLOOKUP([AOP ID]@row, {2023 AOP Entry Range…
-
Return First Non Blank Value within Children Range
Hi, I am currently getting a circular reference error with the formula below. What I am trying to gather is the first nonblank value within the children of a reference Parent Row. =IF([Company Name]@row <> "", INDEX(COLLECT(CHILDREN([Company Name]@row), CHILDREN([Company Name]@row), @cell <> "", CHILDREN([Company…
-
Is there a workday.intl equivalent in SmartSheet?
I am looking to pull in a google sheet used to manage project schedule that uses a 6 day work week to calculate schedules. For example one cells formula is: =IFERROR(if(E69="Mail Date", F69,if(G69="First", workday.intl(AN69,-4,11,Holidays_USPS), if(G69="Standard",workday.intl(AN69,-6,11,Holidays_USPS),"NA"))),"NA") Is…
-
Overlapping Dates and Auto-Flagging
Hello, I have created a communications plan for my organization. I would am trying to auto-flag (in the Overlap row) any communication entry that has overlapping Start/End Dates of the same Delivery Method. I am using the formula below, but receive the #unparseable error. What am I doing wrong? =IF(COUNTIFS(Delivery…
-
Feeding individual sheets into one report for tracking. Please Help?
I have several projects (individual sheets) and I would like them to feed into one report so a team of people who need to overview all these projects at once can track them (mostly by looking at calendar). Can anyone give advice on how to do that?
-
Is there a way to obscure partial name with stars
For example, is it possible to obscure someone's name or ID after say: 2 characters as a helper column? I.e Smith: Sm*** I.e. G12345678: G1****** or obscure only last two characters I.e Smith: ***th I.e ******78
-
calculating dates from column OR from current date
Hello, I am trying to calculate the number of days that a report is overdue. This is the formula I am currently using, but there are some problems I need help to fix. =IFERROR(NETDAYS([Final Report Due]@row, [Final Report Submitted]@row), 0) - 1 I added the -1 because it is calculating the NETDAYS rather than the…
-
Limiting data set for chart on dashboard
Hi, I have a couple of line graphs that are tracking changes in a specific metric (single column) from row to row. The rows are populated when someone completes a form that gets added to the bottom of the sheet. I'd like to limit the visual of the line graph to just the most recent 4 rows (or 4 most recent data sets). Is…
-
Turn Rolling 12 Month report into Rolling 24 Month Report
Using help from a discussion on Smartsheet Community, I have managed to create a rolling 12 month report, using the following formulas Month: =IF(MONTH(TODAY()) - [M1]$1 < 1, MONTH(TODAY()) + (12 - [M1]$1), MONTH(TODAY()) - [M1]$1) Year: =IF(MONTH(TODAY()) - [M1]$1 < 1, YEAR(TODAY()) - 1, YEAR(TODAY())) However, if I…
-
Formula to Count Across Rows
Hello! Checking to see if there is a way to count across rows rather than down columns. I would like to be able to identify how many times over the year the report was either sent on time, late, or not at all. Screenshot provided for reference. Thank you!
