-
How to Automate workflow to create a summary report to diff between two excel sheets on weekly basis
Using SmartSheet, I want to be able to import excel sheet from sharepoint on weekly bases lets say "week 09" excel sheet name is [TC Roadmap 25ww09 VTS] Then I need to add to end additional +5 columns, these column includes formula(s) which I use to massage data coming from original data to create reports After a week ,…
-
Auto sequence based on date
I am looking for a formula to sequence or auto number rows based on a date and order the sequence number simply 1 through infinity. I would like this formula to work even when I add or remove rows or change the dates. I also can't have any duplicate numbers in the Rankeq formula and has a tie breaker column.
-
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.…