-
How to use OR for COUNTIFS, referencing 2 columns in another sheet
Hi, I have the following formula in a column, =COUNTIFS({Sheet Range 1}, [Column Sample]@row, {Sheet Range 2}, "ABC", OR({Sheet Range 3}, >TODAY(), {Sheet Range 4}, >TODAY() I need to do if column A = 1. column B = 2, (next should be OR) or column C = 3 or column D = 4 Please help out!
-
How automatically update a report date when a project sheet is updated?
I've built out a portfolio where all project plan sheets have a summary sheet that rolls up to a portfolio report along with all the other summary sheets. Each summary sheet has a "latest update" date field field where I have to manually enter the date of any changes/updates are made to the project plan sheet. Is there any…
-
INDEX/COLLECT Giving #INVALID VALUE
I have a sheet set up with a form for employees to enter their name, hours, and week ending date on a weekly basis. In order to create a dashboard chart to compare all employee's hours week-to-week, I've set up a separate sheet to reference the entry sheet with the week ending dates as the primary column and employee names…
-
Extract Date from Modified Date
I am trying to use the Modified Date column in formulas. For some reason, it doesn't appear that this was created as a "Date" column type. Because of this, it cannot be used in any formulas that would allow a date. Is this assessment correct? Also, is there a way of pulling the date out? I've pulled out the Month, Day, and…
-
week formula for retail fiscal calendar
need help with a formula I have for our Week columns. we operate on a Retail Fiscal Calendar, so we're 4 weeks behind the Smartsheet default, hence the "- 4" in my formulas. The formulas work great for February through December, but once January hits, the Week cells populate as -3 (Jan wk 1), -2 (Jan wk 2) and so on...…
-
Countifs and Contains formula combined calculate a 0
Hello, I'm trying to count how many times the word"Blended" shows up in the Class column when the stage column is "proposal". I'm using the following formulas which always renders a 0. Can you please help me correct my formula? Thank you! =COUNTIFS(Stage:Stage, "Proposal", [Class]:[Class], CONTAINS("Blended",…
-
How can I get rid of blanks when using join function pulling in from another Sheet?
Currently I have a column in a timeline sheet with the formula ==(IF(AND(Milestones@row = 1, [% Complete]@row < 0.1), Finish@row + " : " + [Task Name]@row, " ")) That says if Milestone column is checked, and Complete column is less than 100%, then combine Finish Date and Task Name with a : in between. I am wanting to JOIN…
-
I need to combine/create a formula that calls an icon
The below formula searches for duplicates in a column labeled 'pin' and adds a one to a 1 to a column named flag when duplicates are found. =IF(COUNTIFS(Pin$1:Pin@row, Pin@row) > 1, 1) I need a formula that does this but turns the 1 into a smartsheets symbol or maybe the word 'duplicate'; I have tried various ways and have…
-
In regards to the Marketing Campaign Management Template
I'm using the Marketing Campaign Management Template that Smartsheet offers. I'm updating some parameters within the '3: Campaign - Plan' Sheet and have unhidden columns to see how everything is connected. I noticed there is a 'Campaign Code' and 'Campaign Name' Column that was hidden and has a column formula. I looked at…
-
Populate multiple metric sheets with cross-sheet formula(s)
I'm looking to improve how I've set up several metric sheets to make sure data is staying up-to-date. The components of the workflow are here: Sheet 1 = This is the reference sheet that is used in reporting. Our billing department uses it to track claims. It's a basic sheet with a simple automation that moves rows to an…
