How to collect historical information from other sheets

Vanessa Tanner

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?

Genevieve P.

Hi @Vanessa Tanner

The way I would do this is to create an "Archive Report" which uses both of your Archive sheets as the source. This way you can Group by the Client Number and use the Summary feature to count how many rows each client has in both places.

You can set the default for the Grouping to be collapsed so it's easy to quickly find the correct client and expand their rows to see more data, perhaps having the individual rows sorted by Date. See: Redesigned Reports with Grouping and Summary Functions

A Report will also allow you to exclude unnecessary columns, so you could just show the Comments/Information without all the other columns that were copied into the source sheets.

Let me know if this would work for you!

Cheers,

Genevieve