-
Index Collect/match formulas / automatically update
Hi everyone, I'd like to have a formulas which reports what we call "Cost Center" from one/multiple sheet into a Year Planning sheet. I need the formula to give me the "Cost Center" if a cell is matching the name of the collapse one (category) above it. Source sheet (Master Sheet NL) : Year Planning sheet : My formula :…
-
Contains function
I am having trouble with a formula that contains multiple "If(contains)" function. The following returns unparsable. =IF(CONTAINS("52", [Account #]@row), "52", IF(CONTAINS("53", [Account #]@row), "53", IF(CONTAINS(“54”, [Account #]@row)”54”, "")))
-
Percentage formula based on a checkbox.
Any suggestions on how to make this exceptions formula more accurate? Smartsheets keeps adding 10 rows to my sheet with no data causing my formula to be off. I have 88 rows with data and a total of 98 rows. Out of the 88 row 21 are an exception with the checkbox selected. This is the formula I am using. =COUNTIF({Master…
-
Formula Question
I have a list of items for sale in (column A) in a dropdown list to be used in a webform.... IE: Dress $25 Pants $50 Shirt $30 I want to have the total cost of the selected item (single select in the column) populated (dollar amount only) in Column B automatically. If Item 1 (Dress $25) is selected in Column A THEN "$25"…
-
Nesting If Statements - Looking for Results Across Two Sheets to Pull into a Third
I am working with three sheets for this project: Sheet 1 - Open Projects, Sheet 2 - Finished Projects, Sheet 3 - All Info. When a project moves from "Open" to "Finished", it is moved automatically from sheet 1 to 2 (these are raw data sheets that cannot be changed or combined). I need to pull down the status into Sheet 3…
-
CountIF one item is a part of multiple selections
Greetings- Im trying to do metrics and a graph for individual items that may be selected as part of multiple items in 2023. For example, my form has the option to select one or multiple from the list: Kids (5-12) Teens (13-17) Adults (18-64) Seniors (65+) Families General Public Staff Use So a user could select Kids and…
-
INDEX MATCH is returning #NO MATCH or the wrong email address
I have a Smartsheet that I have populated with INDEX MATCH, and it works great. My last column is to pull in the email addresses of all the employees, based on their employee ID that has previously loaded into the Smartsheet via an INDEX MATCH. So my [Site Contact 1 ID\@row is data sourced from an INDEX MATCH already. I am…
-
Formula to look at specific column/cell depending on the current month
Hello, I want to create a dashboard widget that will show employee FTEs for the current month. I currently have a sheet (Capacity Plan) that list out the employee's name, and then has the assigned projects with FTE breakdown as child rows underneath. Then I have row that calculates the total for the month. For the widget,…
-
Formula to concatenate multiple "tab" characters doens't work as expected
I created a formula to try to create an indented structure for reports by concatenating multiple "tab' characters based on the indent level. However, the formula doesn't work as expected. Instead of ending up with multiple "tab" characters, the output is either an empty string (when lvl=0 or lvl=1) or it's the number zero…
-
What wrong with this IF OR Loop statement
=IF(OR([Site]@row= "Kelowna", [Site]@row="Vancouver", [Site]@row="Victoria"), "British Columbia", IF(OR([Site]@row= "Halifax", [Site]@row="Moncton", [Site]@row="St Johns", [Site]@row="Grand Montreal"), "Eastern", IF(OR[Site]@row= "Central", [Site]@row="Tannis", [Site]@row="SW Ontario", [Site]@row="Toronto") , "Ontario",…
