-
Help with Sheet Summary Formulas to Create a Metric Sheet and Dashboard Chart
Hi! I'm managing a retrofit project and the end goal is to create an executive dashboard highlighting key metrics in the form of charts and/or graphs. From what I've found via searches, it seems like I need to utilize the Summary function on a sheet (my rollout schedule, in this case) in order to pull metrics the data into…
-
Formula to calculate tasks based on % complete
I have a list of deliverables with a % complete column and I want to calculate the total number of: completed (at 100%) in progress (between 1% and 99%) not started (0%) I am using the following formula for completed and it is giving me 0 results when I know i have 4 completed =COUNTIF([% Complete]2:[% Complete]56, "100%")…
-
Limit the use of a dropdown selection to only a single row in a column
Hello, I've developed a sheet that our Project Manager use to generate a Certificate based on a number of criteria. So the Certifcate (a dashboard) pulls the correct data from multiple sources, the user needs to simply select "YES" in the appropriate row per the below screen shot. Can anyone suggest a way that limits only…
-
vlookup with date
Hi everyone I am currently doing a vlookup to a reference column with a date format, to another sheet in a specific cell but my column is in text/number format. i got this error "invalid column value". Is there a way i could convert the date value to a text or number? thank you
-
SUMIFS with Multiple Criteria
Hello! My ultimate goal is to be able to get a sum of the inventory per item, by month. For example, I would like a formula to return the amount of Control Panels there are in June 2020. Based on the screenshot below, the result I should receive is "2" as is only one project in June 2020 and the project has 2 Control…
-
Stop conditional formatting on parent rows
So we're trying to highlight when tasks are past due, so we set up conditional formatting to highlight rows with due dates = in the past and the status = not complete. The issue is this is also applied to parent rows, which have a "due date" because it auto generates from the tasks under it but since it's not a task, it's…
-
Flagging/Removing duplicate rows that do not include comments
We have a newsletter that gets sent out, and tracks if anyone opens or clicks through them. That Excel file is uploaded to Smartsheet. We then follow up with anyone who interacted with the newsletter, and follow ups are tracked with comments. Is there a way to delete duplicate rows based on whether or not there are already…
-
How can I count dates in the past/future?
Hi, I'm trying to create a formula that counts certain actions in a column based on their date. Say, if there are three dates: 5/15/2020, 5/20/2020, and 5/30/2020, I want to be able to know which ones are in the future and which ones are in the past. In this case, the counter should return 2 dates in the past and 1 date in…
-
Custom auto-number system
We would like to create a reference, auto- number system that takes the Facility ID entered, and the date of incident, to look something like this. (####- 05/15/2020) Does anyone know if its possible to do this? Thanks in Advance!
-
Licensed user - how many workspaces can I create / share?
I'm working on developing a template workspace for our PM to use, that will eventually roll up to a PMO workspace. We plan on getting licenses for the PM if we go to production after the pilot. Each PM has multiple clients so they would have multiple workspaces. My pilot plan is to create workspaces for them and make them…