-
COUNTIF with Parent rows only, exclude children
I want to create a COUNTIF function that will count only the Parent row of a status column in a project tracking sheet. This is to create a metric sheet that will show all project statuses by each project manager's sheet. There is a status column using Green/Yellow/Red/Blue identifiers, and children rows need to include…
-
I'm getting an Error - Invalid Value for a formula - Need help
I have Sheet 'A' - which has Office numbers in a Column called Office No. (Column Type - Text/Number) And I want to get Employee Information in a Column called Employee Info (Column Type - Text/Number) The Employee Information is in Sheet 'B' which has the following columns Office No. (Primary Column) = Similar info as…
-
Reports vs. Sheet (reporting / dashboards)
I am struggling because I have a very large SHEET that has many many data points that I have created a REPORT to create a BUDGET summary and projects for Quarterly Portfolio review(s); however, NOW I have to create a REPORT from this REPORT so I can create WIDGETS / Charts on a Dashboard; however, I know it isn't a thing…
-
Hi - I need help with this formula. I am getting UNPARSEABLE error with it each time
The formula is =IF([Percent Complete]@row = 1, "GREEN"), IF(AND([Percent Complete]@row >=0.80, ([Percent Complete]@row <1), "Yellow", "Red")). What I am trying to get. If Percentage is 100. RAG column should say Green. If Percentage is greater than 40 it should say Yellow and if it is less than 80 than it should Red.
-
Add or subtract Dates in Smartsheet
Hi All, I am trying to add a field where I can add 3 days to the end date column. For example the end date is 01/23/23 and I need to add 3 days to this in a new column. So the formula I used is =[end date]@row + 3. When I do this, I am getting the following error: "Invalid Column Value" Can someone tell me what I need to…
-
Problems with Formula: COUNTIFS to not include certain text
Hello. I am trying to create a formula that helps me to summarize the information on my master sheet so I can create a dashboard. I am trying to sum up the number of courses between published and archived and the formula would collect data from two different columns (status and product type). Is it possible? I tried to…
-
How to use advanced formulas to pull information from two sheets into new (third) sheet
Hello everyone! I am hoping to get some tips/advice on using Index Match/Collect (I think is the right route). Currently, my company has one of our data sources tracked and managed through smartsheet. My goal is to make a new sheet, that is populated from pulling the data from our primary data source BUT I want to filter…
-
Issue with checkboxes and requesting update
Hello, I have individual columns for assignees/contacts and a completion checkbox for each, which will go through a request update. The last column checks if all the checkboxes are full and provides a completion status. In case the assignment has less assignees than the total 10 slots, I put an =isblank(@cell)=true…
-
Complicated IF this THEN that FORMULA
So, I have a pretty complicated formula request: IF [Approved/Contracted Budget $$ (Total)] IS blank look in [Per Pt Costs (Submission)] Column and take that Number and put it in [Per Pts Costs (FINAL)] Column; however, if [Approved/Contracted Budget $$ (Total)] is completed, use that amount ($$) and put it in the [Per Pts…
-
Recording Week Tasks Were Completed
Hi all! I'm back with an old problem that rattled me a few months ago. Basically, when a task is completed, its date of completion is saved. For the sake of being able to a process this data in a more comprehensive manner, we want to take that date, and assign it a "weekOfCompletion". Another note, is that the weeks here…
