Hello again, I have created this worksheet:
Using [ID#] and [Created Date] as reference, how do I display each value under [Entry 01:Entry 03] to fill in under each of the concerned rows? Expected output:
Any help will be greatly appreciated.
It may be easier to do this on the source sheet before you send it over to this final sheet. Make sure new forms are populating at the top of the sheet.
Insert a text/number column called "Number". In the three rows below the bottom form entry, enter the numbers 1, 2, and 3 on separate rows.
Insert a text/number column called "ID" and use this column formula:
=IF(Number@row <> "", INDEX([ID #]:[ID #], 1))
Change the Primary Column name to "Display Name" and use this formula:
=MID("!" + SUBSTITUTE(INDEX([Employee Name]:[Employee Name], 1), CHAR(10), "!") + "!", FIND("~", SUBSTITUTE("!" + SUBSTITUTE(INDEX([Employee Name]:[Employee Name], 1), CHAR(10), "!") + "!", "!", "~", Number@row)) + 1, FIND("~", SUBSTITUTE("!" + SUBSTITUTE(INDEX([Employee Name]:[Employee Name], 1), CHAR(10), "!") + "!", "!", "~", Number@row + 1)) - (FIND("~", SUBSTITUTE("!" + SUBSTITUTE(INDEX([Employee Name]:[Employee Name], 1), CHAR(10), "!") + "!", "!", "~", Number@row)) + 1))
Then set up a copy row automation to run when the new "ID" column changes to any value with a condition of the "Number" column not being blank.
index(collect()) will do what you want.
Use collect() to gather the display names where the ID number matches the row you are on, then use index() to choose the first entry under "Entry 01", the second under "Entry 02", and the third under "Entry 03".
https://help.smartsheet.com/function/collect
https://help.smartsheet.com/function/index
Edit - I misunderstood the request and reversed the desired behavior. Use @Paul Newcome 's answer, he got the order correct.
I would insert an auto-number column called "Auto" and then a text/number column called "Row" with this column formula:
=MATCH(Auto@row, Auto:Auto, 0)
Then you would use something like this:
=INDEX([Entry 01]@row:[Entry 03]@row, 1, COUNTIFS([ID #]:[ID #], @cell = [ID #]@row, [Created Date]:[Created Date], @cell = [Created Date]@row, Row:Row, @cell <= Row@row))
Hello again @Paul Newcome , Thank you for your inputs but the INDEX-ing didn't properly work:
I've jumbled the entries and this happened:
With the above as references, it seems the index is pointing "always" on the last column of the range.
Which one is your Auto-Number column? How are you populating new rows?
Hello @Paul Newcome : Thank you for reaching out. Here's the premise: 1. I have a form that allows users to select multiple names from a dropdown menu. Once the form is submitted, each record (or row) logs the selected names along with the corresponding date and time:
2. The selected names are then parsed and separated into individual columns. Notably, the entries are arranged alphabetically during this separation process.
3. These separated entries are then pushed to another sheet, generating multiple identical rows—one for each name submitted in the form:
4. Using [ID #] and [Created Date] as reference keys, each separated name is mapped and placed into the Display Name column of its corresponding duplicate row.
[ID #]
[Created Date]
Display Name
However, the INDEX() function consistently retrieves only the value from the last column in the specified range, regardless of row context.
INDEX()
5. I tested this by manually rearranging the order of entries in the source sheet. The test confirmed that INDEX() always returns the last value in the range, not the intended individual entry:
I have two options to consider:a. During the value separation process, avoid placing the separated names in alphabetical order, so that the original selection order is preserved. However, this is currently not feasible because the operation enforces alphabetical sorting to begin with — it just ends up that way.
b. When the identical rows are copied to the target sheet, the INDEX() function will initiate to map and display each separated value to its corresponding row under the Display Name column. (This is where I am stuck :-/) I hope the above makes sense.
I came across a similar post last week that used the same formulation - awesome help! Thank you again!
Happy to help. 👍️
ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.
