-
Can I use Index/Match and Left functions in one formula?
Hello, can someone help me with the left function within the index/match? This is what I tried but it always returns NO MATCH: =INDEX({Master Job SO}, MATCH(LEFT([Job Number]@row,5), {Master Job #}, 0)) I also tried creating a helper column, Job Prefix, using =LEFT([Job Number]@row, 5) and using that result in the SO…
-
INVALID COLUMN VALUE
I am trying to pull a due date based on a prior cell's value. Example: Column "Financials Due Date" has a value of 10 In Column "Partners Due Date" I input the following formula but receiving an error. I have the column set to Date. =DATE(YEAR(TODAY()), MONTH(TODAY()), [Financials Due Date]@row)
-
Sheet Summary Formula - All Completed Tasks in the last 30 days
Good morning, I am trying to add the total number of closed items within the last 30 days. I have used a lot of the suggested formulas in past posts, but it is still counting ALL completed tasks (72) instead of the tasks completed in the last 30 days (40). I'm not sure if it's because it's a sheet summary and not within…
-
Color Symbols Based on Number Ranges
Is it possible to have a red/yellow/green circle symbol appear in a column if the cells of another column meet a certain range criteria? For example, I want a green symbol to appear when a cell has a number between 0-45, yellow 46-75, and red 75+.
-
Compressing based on a column label different row data points in one cell
Hi all. I am trying to do a rather complex operation on Smartsheet, generally performed in programming. I have two different sheets: one for Departments and another one for teams. Both sheets can be linked through the interrelation they have, although the teams sheet have multiple times the department across rows (E-G:…
-
Renaming (multiple) values from one column into another column
I am looking to rename certain values that I have in one of my columns. The original column contains values that are accepted in our database (intended for database input), and the new column would be the same value with a descriptor that is intended to be used by product managers. The second column should output all of…
-
How to divide (any math operation) a regular number by number in a currency format?
Hi, I have one column written in a currency format. Lets call this column A. Another column written in a regular number format B. When I try to create a column formula in Column C like: Column A/ Column B, I get an error called invalid operation. As you can see in the screenshot below, that formula I have in the Test…
-
Filter rows under parent row?
Good afternoon, I was wondering if it was possible to filter rows submitted by a form depending on a priority. For example I have 3 parent Rows that are High, Medium, and Low priority. If a new issue is submitted via the form for a High Priority. Have it automatically sort under the High Priority Parent Row? As of right…
-
#NO MATCH in a index match formula for some values
I have a "filter" table in which I can choose via dropdown "year" and "month" values. By updating the values there, I can update the values in other small table I plan to select individual values considering two labels, "On Time" and "Delayed". When I use the following formula, I get a #NO MATCH for certain months, but all…
-
Is there a formula which can number row hierarchies?
I am looking for a number which can categroise row hierarchies into numbers. I have a column called "Hierarchy" and in each row I want it to generate a number depending on what hierarchy the row falls under. For example, the Parent row would return 1, the child of that row would be 2, the grandchild would be 3 etc etc…