-
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…
-
Toggle for hover over pop-up formula information
I use a lot of cell links and very complex formulas. Often when navigating a sheet I need to look at multiple rows but when I hover over a cell with a large formula it pops up, blocking my view of the information I need so I spend a lot of time fighting my schedules just to get information. I would love to be able to…
-
Show what's next on primary row
Primary row 1 Status Up next:subtask 2 Subtask 1 Complete Subtak 2 Subtask 3 Primary row 2 Up Next: Subtask 1 Subtask 1 Subtak 2 Subtask 3 Primary row 3 Up Next: Subtask 3 Subtask 1 Complete Subtak 2 Complete Subtask 3 Is it possible to have a layout like this. So once everything is collapsed it will show what is up next…
-
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…
-
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…
-
Removing Duplicates while also adding together values from other columns
I have a bit of a complex situation. My company uses SAP for purchasing and we pull an open order report weekly to keep track of each of the buyers orders and delivery dates. I've recently set up a Data Shuttle so we can transition from manually editing the report in Excel to a more fully automated report in Smartsheet.…
-
How to flag duplicate dates within a range
Hi everyone, I'm attempting to build an annual leave tracker for my team. The current build is the employee completes a form with Start and End dates > this updates on a Request Log for managers to Approve / Decline > once Approved, copied to a master log and a separate Tracker sheet keeps a list of all employees, their…
-
Copy data on new rows
Hi Guys I have sheet - SHE Comparison MTD - I want to every month the Data on the screenshot to be copied to the same sheet but on new rows below else if not possible it can be copied to a new sheet, but Now it must say April if we are in April - eg FY2025_Apr 2025. also the Areas must be Process (Apr 24) and Process (Apr…