Want to practice working with formulas directly in Smartsheet?
Check out the
Formula Handbook template to view 100+ formulas, including a glossary of every function and examples of commonly used and advanced formulas.
Product questions?
Ask it here! The community's got your back.
Discussion List
Incorrect Argument set with IF and VLOOKUP
Here is my formula - =IF(Activity@row = "Field", VLOOKUP(Employee@row, {Employee Payroll Info Range 2}, 9, false), IF(Activity@row = "Bakery", VLOOKUP(Employee@row, {Employee Payroll Info Range 2}, 8, false))) I am trying to determine which hourly rate to use based on activity performed.
Formula to surface specified data and date from project task
I am trying to to surface to latest EOT from the task to row 1 on EOT Stage, and respectively the corresponding End Date to EOT End Date row 1. We may state EOT 1 - 5 on the project sheet, but it will only take effect the date is filled in. Therefore, the latest EOT need not necessary be 5. But in the following example, I…
Additional date rollup fields
I need to create 6 additional date fields that function like Start Date and End Date. Namely, the parent should be a rollup of the children. If I set =MAX(CHILDREN()) as a column formula, I can't enter any data into the child rows. Is there a better way than having separate fields for parent and children?
Finding At Risk Calculation
I am trying to find out why my sheet is marking tasks "at risk" and what the formula is. It seems to be random but I'm sure there is some rule or calculation somewhere that I'm not seeing. Can you help me find out where it might be?
Count IFS & Contains
I'm trying to count the number of times a specific string of text is referenced in a column on another sheet. I've tried multiple ways, but it doesn't seem to come out right - =COUNTIFS({KPHI: SHP Master Patient Tracker_ProcType}, "TAVR", {KPHI: SHP Master Patient Tracker_Comp@Proc}, CONTAINS("Arrhythmia, 1st Degree AV…
Progressive totals working with column formulas.
Background: our business has Building Managers (BMs) that we provide to a multitude of our clients on a contracted hour/week basis. The BMs have a fixed weekly work hour limit of 38 hours per week. Our various clients have standard attendance hours per week that do not align with the weekly BM hour limit. This leaves us…
Counting the occurances of distinct email addresses that are unknown ahead of time...
I am trying to build a sheet to track panel scoring for an exam. So assume that the candidates name is James Kirk (kirk@starfleet.org). After 3 panelists have entered their scoring for Kirk@starfleet.org , I want it to send a notification to me. I cant hard code any email addresses because I will have new candidates all…
Is there a way to copy conditional formatting to multiple columns?
Forms and manual addition to Sheet
Good afternoon, I have a tricky question. I have designed a form to create a dynamic drop-down experience using columns for each option (kept hidden) and then using a column Join formula in a helper column to collate the results (visible). The issue I will come across is there will be users who will opt out of using the…
HELP!
I am trying to match asset number hours that automatically come in via form and pull them into a separate data dump of current inventory. I only want the highest value since the number of hours are reported daily.
Help Article Resources
Trending in Formulas and Functions