-
Cell Linking? Index/Match?
I have 83 project schedules in a folder. I have a 'Key Milstone' Tracker with project locations in the primary column and all the milestones I need to grab as columns. Each milestone I need is in the exact same place on each of the 83 sheets. Is there an easier way of linking the cells other than sheet by sheet; or some…
-
RYG Formula for Multiple Criteria
I'm out of practice with formulas. Any advice/direction would be appreciated. Thanks! Requirements: If status = Complete or N/A, then "Green" If status <> Complete or N/A, and finish date is past due, then "Red" If status <> Complete or N/A, and finish date is approaching within 5 days, then "Yellow"
-
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…
