How to collect historical information from other sheets

Hello All,

I currently have a request to help a team with some Smartsheet questions and have hit a wall on one of their request.

They have three sheets

Sheet #1 – Current client list.

Sheet #2 – Archive sheet

Sheet #3 – Alternative Archive sheet.

A form is submitted in when a client wants to order items. We have helper columns in this sheet – Helper #1 – a duplicate finder has this client ordered from us in the past? Helper #2 – How many times client appears on Archive sheet. Helper #3 how many times client appears on Alternative Archive sheet. Each client has a client # that is unique to them. I want to bring back the historical order information/comments for the team (this can be a new sheet) because eventually the rows in the current client list will be moved to one of the archive sheets and currently there is no way when you move a row to only move certain columns over.

Any ideas on how I could accomplish this? Also Client #1234 could have ordered 3 times in the past. 1 time could be presented on the Archive sheet and 2 other occurrences could be on the Alternative Archive sheet.

Additionally I tried to do a =INDEX(COLLECT({Client #}, {Helper #1}, 1), 1) function where if Helper #1 equaled 1 it would bring back the client #. I am not sure how to formulate this so it brings back a list automatically of all occurrences. Right now I have to list 1,2,3,4 for the row position to bring is back. We have 240 clients in cue and that can change so I don’t want to manually put in the row positions. What am I doing wrong?

Would Data Mesh help with this?

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!