-
Return a value if an item matches
Hello - new to formulas & struggling to get one to work. I have two sheets: One with the values in column format (Payer CSS Responses by Initiative) - this was done so specific drop down options can be chosen; and one that contains those same columns but in row format so that it easy to view (Payer CSS Template). What I'm…
-
My formula doesn't work when I choose the first of the month as Effective Date
this is my formula: =IFERROR(DATE(YEAR([Effective Date]@row) + ROUNDDOWN((MONTH([Effective Date]@row) + ([Contract Term (Months)]@row)) / 12, 0) + IF(IF(MOD(MONTH([Effective Date]@row) + ([Contract Term (Months)]@row), 12) = 0, 12, MOD(MONTH([Effective Date]@row - 1) + ([Contract Term (Months)]@row), 12)) = 12, -1),…
-
Help with formula
I am setting up as simple tracking of an annual communications plan and have a Platform of Engagement column with about 12 drop down platforms to select from. I want a formula to track the amount of engagement on each platform selected within this column. The following formula works counting for one platform of engagement,…
-
Smartsheet Formula for Multiselect cell based on time
Hi, I have created a form for our clinical therapists to enter type of service or referral provided for particular clients each week. I want an automation that takes this data and lists services provided to a particular client on another sheet if it was completed in the past week. The formula I've been working with is:…
-
Formula for Hours Scheduled by Month
We're trying to determine how many hours we have scheduled in a month for a particular project based on the start and end date of the project. I can't quite figure out how to set the MAX/MIN without the EOM function. Can someone please help?
-
Working with data betwen two sheets and mutliple columns
Good Morning All, I am having with an issue matchhing data between two sheets based on certain criteria. 1st sheet is a list of form responses. I have a name of requestor column. I need to complete two objectives based on this requestor. See if their name exists in one of two columns on another sheet. 2 if their name…
-
Formula for counting publications in a calendar year
Hi, Here is a background on what my ask is: We track publications on Smartsheets and the publication activity column has the following drop-down options: Journal Article Meeting/ Conference Abstract/ Poster Book chapter Non-authorship contributions to publications (acknowledgement) Non-scientific presentation (podium/…
-
COUNTIFs for multiselect drop downs
Hello, I have a set of form responses where 350 colleagues have given their job level, and provided their 5 personal goals (in separate columns), and which of our organisational targets their goal aligns to. They have selected the alignment to our organisational targets from a multiselect dropdown, and some people's goals…
-
Formula Help - Average IF
Hi everyone. I am hoping you can help me. I am trying to average the time it takes individual case managers to complete a case between specific dates. The reference cells are {CM Text} in the main sheet and it has to equal the name in [Case Manager]@row in the reference sheet The average comes from the reference identified…
-
Header Formatting for Formulas
How do you convert the first row to mirror the header row? The Smartsheet template that we are utilizing requires us to use the first row in all formulas to pull information appropriately. When we type in the header name, we receive error messages as if the sheet cannot find the information. We are thinking that there is a…
