-
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…
-
Counting prior month data
Hello, I'm trying to report on prior month data points within a Dashboard I created. I've built a metric sheet off a "master" sheet. I've attached a screen shot of the metric sheet and a screen shot of the master sheet columns. Here is the formula I've been using to capture metrics - it needs to be changed to capture prior…
-
Changing a cell based on text in two different cells, nested IF/AND/OR formula not working
Hello! I'm trying to change the text in a cell based on answers in two separate cells. I basically need approval from two separate people in order for a request to be approved and if one of them denies the request I need the status row to show "denied". The formula below works for this except in the case where the current…