-
Look up blank cell and add a value in another column on the same row
Can someone show me how to write a forumla that will look up if a cell in the column "Critical date" is populated enter 1 in column "CD2" on the same row. Essentially what I'm trying to do is run a report that summarises all my critical dates but I can't select that date column in my report. If there is a better way I'm…
-
Formula Assistance - Duration of Activity at Specific Rate
I hate math, so am asking the Community for some help. Looking to build what I think should be a reasonably simple formula for someone that doesn't hate math. Here's the Word problem: We have equipment that scans files for malware at the rate of 77,000 files per day. Apparently it doesn't matter if the file size is big…
-
IF formula or Nested IF
I'm trying to create a formula using the Flag symbol and a % Allocated to Project column. I would like the flag to raise if the % Allocated to Project column is 50% or less. If greater than 50% or Blank I would like the flag to stay down. I thought I could accomplish this with =IF([% Allocated to Project]@row< 0.5, 1, 0)…
-
SUMIFS for multiple criteria
This should be fairly simple, but I am spinning on this syntax. Trying to sum my "Invoiced" column if the Year Column = "2019" and the Type Column = "Expense". I have tried a few variations, but this is what I think should work? =SUMIFS(Invoiced:Invoiced, Type:Type, Expense, Year:Year, 2019). I have tried quotations around…
-
Conditional lock
Hello, I am trying to develop a shareable sheet in which some tasks are listed along a row and check boxes are used to indicate a task has been completed. Would it be possible to lock these checkboxes if its previous one haven't been checked? In this way a process flow would be enforced. Perhaps the solution would include…
-
Graph 3 months running totals
I have a worksheet that returns the count of requests for a given week, and I have set it up to work for a year. What I envision is creating a graph that illustrates a running three months worth of requests at a time. I would like the graph to automatically update so that the range always displays the last three months. In…
-
Traffic Lights/cell colour based on corresponding numeric value
Hi, can anyone please help? I want to set up an alert system in SmartSheet that turns a cell green (and labelled 'on track') if a corresponding cell is >90%, yellow ('needs work') if 70%-89%, orange ('at risk') if 40-69%, red ('behind') if 2-39%, and blue ('not started') if <2%.
-
Text in quotes not case sensitive anymore?
Has anyone else noticed that text within quotes is no longer case sensitive? I had noticed that column names weren't case sensitive anymore a while back which wasn't an issue because when you name a column, case doesn't matter. You can't name a column as "Column" and then name another column as "column". But this is the…
-
IF formula help to gray out a status
Hi Community...having issues with this formula working for the "Gray"Status. The other criteria is working so can't figure out why I can't get gray status to work. =IF([Actual Progress]10 >= ($[Week 1 Target Progress (Aug 22)]10 * 0.8), "Green", IF(AND([Actual Progress]10 < ($[Week 1 Target Progress (Aug 22)]10 * 0.8),…
-
How to Cross Reference Data from Another Sheet
I'm trying to link data from a column in one sheet to the similar column in another sheet. What is the best way to do that? I can't seem to get the VLOOKUP formula to work properly. Thank you.