-
Index Match formula issue - unparseable error
Very new Smartsheet user. Found the Index and Match formula that I believe is the correct one to use for my case. My sheets have more fields than listed just a few to give idea of information. Sheet 1: Appointments Select fields: Name, MRN, Appt #, LLL Language, MRN Language lookup (columns formula will return value in)…
-
Formula help with Index Match and Contains
Here is what i have in 2 ways but neither work: IF(CONTAINS("X1", [Model Type]@row), INDEX({Other Sheet Range}, MATCH([Email 2]@row, {Other Sheet Email Range}, 0)), "") IF(CONTAINS(INDEX({model}, MATCH([Email]@row, {assignee}, 0)), "X1"), INDEX({serial number}, MATCH([Email]@row, {assignee}, 0)), "") My goal is to retrieve…
-
Join Collect formula that excludes the current row from the results
I'm trying to create a formula that creates a list of duplicates in my sheets, I've gotten this formula to work but it's showing the original row within the list of duplicates. I tried adding an auto number column and including a condition that the list shouldn't include the current row. However, the addition of this to…
-
Convert VLOOKUP to INDEX/COLLECT?
Hi, I am trying to calculate the fees for a project based a list of fees from another sheet. My current formula works when only one product is selected but does not work for multiple products. Would converting this to INDEX/COLLECT work? And if so, how would I do it? Current formula looks up the value in Project Type/Scope…
-
Record a date of last attached file
Hi all, I have a main sheet that gets updated every time we manually attach a new file to it. I have a number of reports and dashboards that automatically update upon this manual attachment. I would like to record the date upon which the main sheet was updated specifically via attachment of this file (we update the sheet…
-
How Do I add Sum(Children) at the bottom of the children rows
Hi, How do I use the =Sum(Children) function in Smartsheet to sum child rows and display the total at the bottom. I usually create a parent row, then add rows beneath it, indent them and add the formula =Sum(Children) to the parent row to calculate the total. However, that structure was confusing for users, and they…
-
A way to place the Row ID (for APIs) into a cell value with formula?
For clarification, the ID I am after here is the Row ID you can view in the properties of a row. I'd like to be able to do something like have someone export a sheet and send it to me for processing, and have a pointer directly back to which records there might need a resulting update, to be done via automation, that would…
-
Multiple 'If/Contains' Formula
Hi! I am compiling a list of IP addresses that are associated with phyical sites. I am able to create a basic IF/Contains formula to populate the 'Location' column based on the 'IPv4' row. Issue: The issue I am running into is that I have many locations. If i keep the list small (20/30 locations), the formula works…
-
Index Collect Question
I am trying to use Index Collect for the following - and I am stuck :-) In the column "Validation NL" (upper table) I like to display the value of the column "Jira Status" if the value in the column "Epic" = "GBT-704" the value in the column "Req ID" matches the value in the column "MMP ID" This is the formula I tried but…
-
Having trouble with an Index Collect with multiple matches?
Hello all, please let me know if this makes sense what I am trying to accomplish as I have been stuck on trying to get a fix for this. I am working to create a sheet that looks at an export out of a dsp and checks to see if those are elements match with the information in our smartsheet - kind of like a QA sheet to do…