-
Pull latest formula help - index/collect/max
Trying to use Max(Collect) but keep getting #INVALID REFERENCE# =MAX(COLLECT({Sheet A Date}, {Sheet A- Date column}, {Sheet A- Equipment Name column}, [Equipment Name]@row)) But having trouble applying these to my scenario. How do I update these for my scenario? Sheet "Device Master" has Device Barcode field (and lots of…
-
Join + Distinct formula in Global Updates
Hi - Trying to update 1 specific cell across all my sheets in Control Center with the below formula in global updates, but it does not register the last portion of the range. I get the error pop up "The formula you entered contains 2 or more different row references. If you're attempting to replace this formula pattern in…
-
Listing Sheet Summary Fields
Is there a way to get a list of all of the sheet summary fields in a sheet with their formulas? For example SummaryField1 = COUNTIF(Field1,="1") SummaryField2 = COUNTIF(Field2,="2")
-
Vlookup - copy a date to another sheet
Hey, I would like to use a vlookup to copy a due date to another sheet. The vlookup works in other cases, just with the date it doesn't and I get #INVALID COLUMN VALUE as a result. I know it has to do with the date itself but I am unable to figure this out myself :-) - can someone help? The formula:…
-
Add an ISBLANK Function?
All, I have the following forumula: =IFERROR(INDEX(COLLECT({INDEX Rebate}, {MATCH Prod Name}, [Product Name]@row, {Report}, @cell = 1), 1), "No Rebate") I want to add an additional check, so that if the matched cell in the {INDEX Rebate} reference range is blank, the output of the forumla is also "No Rebate". All my…
-
Keep Getting Invalid Value for this Formula
Hi, If someone proficient in smartsheet can help me out on this. I keep getting #invalid value for my dates that I enter from January to March. But from April onward the (target submittal dates appear). =IFERROR(DATE(YEAR([Project Start Date Tentative]@row), MONTH([Project Start Date Tentative]@row) - 3, DAY([Project Start…
-
Check for Payments Requests for Same Project
I have a sheet that collects forms submitted by Program Developers to request payment for work on program development. For the Payee, they can select a [Role] that is either Instructor, Subject Matter Expert (SME), Co-Instructor, or Co-SME. If they choose Co-Instructor or Co-SME, they are asked to enter a [Percentage] of…
-
AVGW Formula for Multiple Cells in One Column
I am trying to apply weights to multiple cells in a "% Completed" column. My current formula just averages out the values for the selected cells: =AVG([% Complete]3, [% Complete]5, [% Complete]7, [% Complete]9, [% Complete]11, [% Complete]53) I'd like to apply weights of 10% to row 3, 5, 7, 9, and 53 with a weight of 50%…
-
Count or Sum formula
I want to sum the columns that contain 1 and have tried a SUMIF and COUNTIF Formula, as well as using the AI formula generator within the sheet - nothing is working. As you will see from the screen shot below I would be expecting this column to return a value of 7 but, it just returns 0. I know it's going to be something…
-
Calculating time
Hello All, I'm a new user to Smartsheet and am trying to figure out the formula for calculating time. I have a start time column and an end time column and am trying to figure out the formula to calculate the amount of time in minutes. Any help would be appreciated!