-
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…
-
Data Table: Automatically Updating Source and Target Sheets
Hi all! Coming to the experts for this… My company has 20k+ employees, so I cannot have all their employee information on one sheet. Instead, I've broken it up into two different sheets: one for executives; and one for non-executives. I'll refer to these as the "main sheets." I also have different sheets for each of our…
-
Dynamically choose column to sum/countif from a wider range?
I am working on some budget tracking. I have a source sheet with each expense account and how much is budgeted for each month (Jan-Dec) and it goes back years. I want to display the info on a graph that show Jan-Dec and the sum of all expense account budgeted amounts in each cell. Right now in my metric sheet I have the…
-
Merging Columns together from different sheets.
I have two separate sheets, both tracking job #'s. They are different job numbers and I want to bring those numbers over into another sheet with the column title DRR/Job # (the two different job #'s in one column). Essentially we are tracking the amount of different jobs per persons name so I guess also, how can I…