-
Countifs with Multiple Criteria
Hi - I need assistance with building a =COUNTIFS formula with multiple criteria. Here is my current equation (that I am getting a '0' on) Current Equation: =COUNTIFS({B23 Delegation Registration Status Region}, "SOAF", {B23 Delegation Registration Status Registration G}, "Athlete" + "Unified Partner" + "Medical Staff" +…
-
Automation / Formula to separate text from flight information
Hello, Is there a way to separate the text from a flight booking straight from our travel system into separate Smartsheet columns? This is the data we receive in our travel system when booking a flight : BA 990Y 10NOV 4 LHRBER SS1 0710 1005 I would love to be able to move this data into several columns in Smartsheet:…
-
Formula to countdown contract expiration dates
I have a sheet that lists all active contracts for my department. The sheet houses the contract details such as Contract Name, Contract ID #, Vendor, Start Date, End Date and Extension Date, etc.… I am currently using =[Expiration Date]@row - today() as the formula in an adjacent cell to countdown the days until the…
-
Index Match does not work for hyperlinks to Smartsheet Item
I use Index Match to pull the project meta data from another sheet and one of the data elements to pull is the Project Dashboard Link, which is a hyperlink to a Smartsheet Dashboard. The link is not being pulled across only the Display Value/Text. Is there a way to pull the link across? I read somewhere that the workaround…
-
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…
-
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…
-
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…
-
Can I use a formula in a checkbox cell to auto check and also be able to manually check the box.
I am using empty checkboxes to show a task needs to be done. Only certain rows will require this task. So I wrote a formula to auto check any box that will not need to be done. That way a report will only tell me empty checkboxes. Problem is, I need the users to be able to check those boxes when they are done with the…