-
@cell Reference for Whole Column
I tried to create a formula with a structure like COUNTIF(column:column, YEAR(@cell)=2021) and was getting an error. I realized that if I changed to COUNTIF(column1:column23, YEAR(@cell)=2021), the formula would work. It seems that the @cell reference does not work when a whole column is selected. Does anyone have a…
-
Counting number of selections made in a multiselect column cell
I have a sheet that we are using to track partners that attend various meetings. The organizers of the groups select the partners that attend a meeting from a multi-select dropdown list. I need to be able to count how many partners are marked in each of these cells individually. For the screen shot below what I would need…
-
SUMIFS
Hi, I'm looking to sum the cells in a column if another corresponding column is either of two values. I'm getting #UNPARSEABLE though! 😕 =SUMIFS({Data Range Spend}, {Data Range Award Type}, OR(@cell = "Direct Award”, @cell = "Mini-Competition”)) Any thoughts welcome! Thanks, Derek
-
Nested If & And error
Hello, I'm relatively new to Smartsheet and am having an issue building a nested if/and function for one of my grids. I have 2 columns ("Effort" & "Impact") and am trying to get 4 unique values based on the numbers - If Effort@row >12.5 and Impact@row<45 show "Kill" If Effort@row>12.5 and Impact@row>45 show "Challenge" If…
-
Setting up an Office Status Indicator
Hello! I am trying to setup a sort of "office status" dashboard for staff. Can anyone guide me on a good way to do this? I am a bit stumped as to how I can get some of the info across. I have one sheet with PTO information: PTO Start (Date) PTO End Date (Date) Currently on PTO (True , False) Name Request Duration (Whole…
-
Formula to set a color of an icon depending on the % brackets
Dear Colleagues, I am trying a formula with dot icons to conditionally set the color if the budget is exceeded by: 0%<10% a dot remains green 10%>15% a dot becomes yellow 15%> a dot becomes red I have tried this formula but my colors are wrong. Please see an example. I suspect that my formula is incorrect because the 0.19%…
-
ADD LAST COMPLETE DATE
I have the following columns that are linked to another smartsheet: Citrix Status - Network Status - PC Status - Phone Status - Completion Date When all columns have the "Complete" status, the Completion Date will register the date of last column that changed to "complete" status. I really appreciate any help! Rob
-
Having overall sheet health based off of parent and child row?
Hi - I know this has been asked and answered similarly, but I am struggling with actually implementing the formulas. I have created a simple sheet that I would like to have a visual R/Y/G/Gr marker at the top of the sheet that is a roll up of the all of the parent rows below. I am basing the formula structure off of this…
-
Nesting ISBLANK within IF(AND) Statements
Hello, I have successfully established a formula link between two date columns (Start & Finish) and my 3 status trackers (Completed, In Progress, Not Started). The formula I have used successfully is: =IF(AND(Start@row > TODAY(), Finish@row > TODAY()), "Not Started", IF(AND(Start@row < TODAY(), Finish@row > TODAY()), "In…
-
How can I stop my Sheet Summary from changing the location of a cell when form data comes in?
I am creating an inventory form that populates data into a sheet on "Top of Sheet". I want to create a summary sheet that reflects how much of an item we need vs what we have entered on the count (via the form). My issue is, whenever a form gets filled out, it also shifts the cell I am trying to reference down. I want it…