-
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…
-
How can a calculate a due date based on weeks?
I would like to calculate a due date that is 4 weeks before a launch date. The below formula helps me calculate based on days, but I would like the due date to be 4 weeks before the launch date. =[Launch Date]@row - 28
-
Formula Help
Is there a formula to pull the first letter of cell contents? I'm wanting to populate the "Subcategory" column automatically instead of manually Thanks!