-
NEW! Your survival guide to formulas is here. Meet the Formula Handbook.
Hi Community, Whether you’re summing child rows, calculating budget minus actual, or doing cross-sheet data lookups with multiple criteria, formulas are a must for tailoring solutions to your needs. The new Formula Handbook is here to help formula beginners and pros alike. This template contains: A glossary of all…
-
Check Box for Current Milestone
I am looking for help in writing a formula (or using automations) so that the most recent milestone where the status is "INV PKT SENT & PSR XL UPDT" will have the checkbox. As you can see, I can write a formula to check the box but then when the next milestone reaches that same status I now have multiple checkboxes in the…
-
COUNTM Formula Help
Is there a way to use the COUNTM formula to count the number of selected dropdown options in a multi-select column (e.g., "Deposit Accounts") only when another column (e.g., "Region") equals a specific value (e.g., "East")? For example, I want to count how many Deposit Accounts have been selected only for rows where the…
-
Calculating Total Cost for Rooming List with additional Hotel Matrix Sheet
I'm trying to calculate the 'Estimated Total Cost' for a traveller staying at a hotel. I have a rooming list sheet and a hotel matrix sheet. The rooming list has each traveller on each row and the '1' represents that they are staying that night. The hotel matrix sheet has the room types, dates, nightly rates and total.…
-
Formula Help: Choosing Most Recent Fields Across Sheets
I’m working with two Smartsheet sheets: Sheet A: Tracks courses taught (each row has 2 instructors + 2 courses — both instructors taught both courses). Sheet B: Lists instructors. I want to display the most recent course(s) they taught, pulled from Sheet A. My challenge: Because Sheet A has two instructor ID columns…
-
Current user metric widget
I have an evaluation form that gets filled out and results can be viewed on a dashboard. I have two, one for the submitter to see only their entries and one for the location being evaluated and the viewers see only the evaluations that are pertinent to them. I have a sheet summary formula that counts the total visits and…
-
Can't get the "Ongoing" piece to work
I'm trying to make a formula, a typical status formula which I do often. But it was asked to include an "Ongoing" status when the % Complete column is at 99%, regardless of other conditions. The problem I'm running into is that no matter how I input it, the "In Progress" piece always overrides it, and I can't quite get the…
-
COUNTIFS for multiple listed characters (not using numbers) in multiple columns
Good Evening Community, Column A contains the formula, see below Deliverables NOT Completed contains all the listed values I will need to incorporate in this formula. Deliverables Completed - (COMMS)/ Deliverables COMPLETED - (PROG)/Deliverables COMPLETED - (DEVE) all contain the same listed values from Deliverables NOT…
-
Identifying data associated with a previous row
Thanks in advance! From a listing of all meetings, I am hoping to pull in the date of the previous meeting. Note, there are no successors/predecessors with this (I've been able to successfully use Paul's post in other areas to pull in the task name and hoping somehow this could be similar?) Has anyone done anything…
-
If Condition
I have this condition which works well (copied from line 89 of my plan) =IF([% Complete]89 = 1, "Complete", IF([% Complete]89 = 0, "Not Started", "In Progress")) I'd like to add two conditions: 1st: if the %complete is 90%, then the cell should change to "Scheduled" What I thought would work: =IF([% Complete]85 = 1,…
-
Best method for linking 2 sets of data together that use forms to collate the information?
I'm working on a project to help devise a solution to track all of our organisation's skills & talent initiatives, plus that will provide us with some useful metrics. The plan is have 2 forms - part 1 that collates all of the schemes' information and then a part 2 that collates information on outputs, but which is sent…