-
Formula Help: Find Value in 1st Row in Same Column, without using Absolute References
I'm trying to create a formula that gives me the value of the first row in the same column. [Find This Value]. There are a few obstacles: The formula is placed in the same column, BELOW value we're trying to find, in [Find This Value]13:[Find This Value]21. So it needs to be a cell formula and not a column formula And my…
-
Help with Index/Match + if blank
Hello, I am trying to create a formula for one sheet to search another sheet based on a PO#, and return the value of the latest PO revision. I'm trying to make this work without adding a helper column to the sample data sheet. Here is sample data: The result I want in a separate sheet: I came up with two possible solutions…
-
HELP! Formula to Find Matching Value Between two Dates in another sheet
I need help creating a formula that I thought would be easy but is turning out to kick my be-hind, and three different Ai’s aren’t able to give me a solution either. Formula Objective: Find the Report Name located on the Report Deadline sheet, using the [Pay Date]@row, which will be a date between two date values on Report…
-
Tally Duplicates Under First Occurrence
I have a count of incidents for each location. The "rank helper" column use to total the number of incidents beside the first occurrence of a name. For example: Bellflower has "2" incidents. I want the first listing for Bellflower to show the "2" in the "rank helper" column and additional Bellflower occurrences not display…
-
Index/Match Formula Referencing Contact Columns Providing Wrong Values
I need help with a formula. Since there is no way to cross-check against a group in a sheet, I have created a sheet listing all users of a particular group and am trying to use a cross-sheet reference formula in my intake sheet to be able to identify when a requestor is not already in the group so that I can add them for…
-
Help with INDEX/COLLECT with a MAX/COLLECT
I am attempting to create a few reference columns using an INDEX/COLLECT function and incorporating a MAX/COLLECT because there is a 1-Many relationship on the RTO Queue ID in the 2nd sheet so I would like to use the row with the MAX Fully Executed Date. Below is the formula I am using: =INDEX(COLLECT({IA Assigned}, {RTO…
-
Index/Collect Two Separate Dates and Compare Them to Return Highest Date
I'm trying to use Index Collect based on a client name to look at two different dates in a separate sheet, and compare them to pull the highest date. We have 1 sheet that we log all contacts, and there are 2 types of contacts "Type1" and "Type2". Then, we have an overall summary sheet that shows the most recent contact.…
-
Need a formula that evaluates multiple cells for data
I need a formula that looks in multiple cells for a value, when it finds a value, do a lookup to find the translated value. I have a sheet that is gathering data in different languages, and we need them all to be translated to English. Below is the snip it of the sheet. So if the form is in English, it will return the…
-
Formulas with INDEX and MATCH function now showing #NOMATCH
All of my columns that are using the INDEX and MATCH function are now showing as #NOMATCH. was there a change made in Smartsheet? what will i have to use now instead?
-
Index/Match Formula Behavior
Hello, I'm encountering an issue with the INDEX/MATCH function. When the value in the cell being match does not find a match and is recognized as text rather than a number, the formula does not return 'no match' as expected. Instead, it displays the last entry in the reference range. Formula in cell Category Index/Match…