-
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…
-
Why is my INDEX/COLLECT formula not working?
I have a very large contact database that has columns of information need to be updated periodically. I'd like to use a separate sheet with form as an activity log, where each new row contains the updated information for a specific contact in the database. To do this, each contact in the database has a unique ID. When…
-
Index match with Left
I'm trying to make an Index/Match formula and I can use some help. I have a Smartsheet with a list of parts (Sheet1). I also have a Smartsheet that has a list of part prefixes and a contact for parts beginning with that prefix (Sheet2). I would like to do an Index match on my parts smarsheet (sheet1) so that any parts…
-
Index/Match vs Index/Collect vs Index/Contains
Creating a sheet to collect and answer FAQs, I am attempting to populate the System/Tech Supporting SME dependent upon the selected system/technology. I have my primary collection sheet and then I have made a reference sheet for some formulas. Relevant columns with fake data from the primary collection sheet. System/Tech…
-
Retrieve value from "Top Parent" when Ancestor level = 2
I have a project management sheet. CSYYXXXX is the header for the project name column Tasks is the tasks column - at the parent row, I just write "Tasks" and then there are child rows below that. Some of these child rows and another level of descendant rows. I want a column formula (to serve as a helper column for various…
-
How can I enter data in a field that has a column formula?
I have a Contact list as a column type on sheet A and it is attached to my feeder sheet B. Sheet A also has a form attached to it and when a list option is selected, the contact list is populated on the sheet. However, if the list option is not selected, then the contact list does not populate. I need to be able to fill in…
-
#Invalid Data Type in INDEX Formula
I am trying to reference a State Abbreviation sheet that has one column the full state name in column 1 and the abbreviation in the column 2. Right now, my form populates the full state name in to my column: "Onsite/Worksite State" and I need it to populate just the abbreviation into a new column. When using the Index…