-
Is it Possible to Use INDEX-COLLECT When a Criteria Field is a Multi-Select Dropdown?
Hi, all; I'm hoping you can help me with this one. Here's my boggle: Sheet A collects Milestone and Parts data from a form and datestamps each entry. Sheet B has columns (Milestones) and rows (Parts) corresponding to the data entered in Sheet A, and the cells pull the date for each entry from Sheet A. For simplicity, we'll…
-
What is the correct COUNTIFS/TODAY formula for past and future 90 days?
I am trying to COUNT the tasks assigned to departments in the past/future 90 days. I am using sheet summary fields for this data. These are the formulas I have come up with but they are not accurate. =COUNTIFS(Department1:Department72, "fsb", [Start Date]1:[Start Date]72, <=TODAY(-90)) =COUNTIFS(Department1:Department72,…
-
Mileage reimbursement Help
I have automated an expense report. This worked great as I did an index/match on the date the expense was for, compared to the year, and reported back the mileage reimbursement rate. =IF(ISBLANK(Miles@row), Miles@row * 0, Miles@row * (INDEX({Mileage Reimbursement master Range 1}, MATCH([F*Year]@row, {Mileage Reimbursement…
-
Flag warning if date is after another within same column
Hi all, Currently having issues with this: I need to find a way of flagging a warning (that'll just say "WARNING") in the thirteenth row of the date column if any of the dates in rows 2-12 are after the date in row 1. I am also struggling to find a concise way of flagging a warning in row 14 if there are any duplicates in…
-
Nested IF with INDEX (MATCH)
Hi there! Hoping to get some help solving an index match issue I am working through. I want to use a formula that will pull a rental rate, from a rate card, based on the rental duration. There are 8 different rental rates possible for each item. Currently I am using, =IF([Rental duration cheat]@row = 1, INDEX({1 Week…
-
Need a Clear Example of Using INDEX and MATCH in lieu of HLOOKUP
Hi. I've found plenty of examples of how to use INDEX and MATCH in place of VLOOKUP in SmartSheet, but no matter how many help topics and forum Q&As I've searched, I cannot find a clear example of how to set up INDEX and MATCH for an HLOOKUP equivalent in Smartsheet. Can someone please help?
-
Populating Entire Columns Referencing Another Sheet
Hi all! Over the last week or so I have been trying to build a report that shows average tasks completed over a given 12 week period. So far I have been successful in creating the formulas for these, which have been working on dummy data. Now I would like to process live data from another sheet. Well ideally I would…
-
Formula to find the average word used in a column
Hi, Is there a formula I can use to find the average "word" used in a column? For example, I want to find the average of these words from the drop-down list: Cheers! Ann
-
Generate List of Unique Values - INDEX? DISTINCT? VLOOKUP?
I'm trying to have Smartsheet create a list of unique data *without* using Reports. Help (-: In the Names table in the screenshot, you can see that there are names manually entered a number of times in random order. That is fine and good. What I'm trying to do is is have a second sheet have a "Names - Unique" column which…
-
Formula for running six-month total points for many employees
I am tasked with designing a sheet to track points that employees (~250) accrue daily (Sunday-Saturday). Point categories are 0.25, 0.50, 0.75, 1.00, and 2.00. The running points total needs to be over six months. I'm not great at formulas and from what I've seen in other Community posts about running six-month totals, the…
