Return multiple values from one search criteria

Paul Newcome

Atlanta seems to have a mix, but (at least what I see in your screenshot) Baton Rouge is just leading zeros. Either way, there is a mix of data types. Try creating a helper column in the source sheet (can be hidden after setting up) and use that column to convert ALL of the rows into text strings.

=[Column Name]@row + ""

(plus quote quote)

Then reference this helper column in your cross sheet reference.

PeggyLang

@Paul Newcome solved my issue. Apparently Smartsheet really does have an issue with leading zeros. I created a helper column in source sheet whereby I added an ‘A’ to the front end of store #’s. Then in my Lookup worksheet I simply referenced the helper column instead of the store # column.
In my Lookup worksheet I created another helper column to strip the superfluous ‘A’ from the store #.

Couldn’t have figure out the formula without you though. THANKS!!

PeggyLang

@Paul Newcome
I was fooled into thinking that Smartsheet was okay with my leading zeros, because it did work as seen above with Baton Rouge. Anyway I created a helper column in source sheet with column formula to add 'A' to store # and then in Lookup Worksheet reference that newly created helper column. Works beautifully now!
To clean it all up in the Lookup Sheet I created another helper column to strip the superfluous 'A' from store # and hid that, so that users will only see the actual store #.
Smartsheet surely delivers a puzzle each and every time I work through a solution. Thank goodness I LOVE puzzles and thank goodness for brilliant minds like yours and all others on this community that are willing to share.

Paul Newcome

@PeggyLang Glad you were able to get it working. Baton Rouge may have thrown things off if ALL store numbers started with leading zeros so that all were being viewed as text strings. Another city may have had none with leading zeros meaning all were numbers. But then Atlanta had to have both which is when the mix between text and numbers actually became apparent.

matthew_sehomes

I am trying to create a sheet that pulls data from my master sheet based on "Assessment Type". I want to pull all entries for all 4 out of the 7 assessment types in order to keep track of the progress of our new hires. I have perused this forum but can't seem to find a solution (or the correct question to ask). I have tried automation and building a report but neither of those options give me what it is I am wanting. The automation only grabbed entries after the date that I set up the automation and the report is too jumbled to make much sense of the data.

One thing I thought about doing was adding a "date of hire" column to the master sheet and pulling the data based off of that instead of the assessment type. In that scenario I could just pull all entries from the last 90 days and filter to the assessment types desired. However, each team member has multiple entries and manually entering the date of hire for each entry is unrealistic.

Any suggestions are greatly appreciated!

Master Data Sheet

Destination Sheet

Paul Newcome

@matthew_sehomes What is the end goal for this data? What exactly about a report makes it hard to use?

matthew_sehomes

https://community.smartsheet.com/discussion/comment/449869#Comment_449869

I want to be able to show the progression through assessments for each TM in chronological fashion. That is, benchmark 1, benchmark 2, 10-day, then 30-day. Ideally I would like to create some kind of graph that shows this progress over time sine each assessment has a date attached to it as well. With the report, I am unable to filter/sort in such a way that organizes the data in this way. I would also like to be able to sort it even further by department and share with the respective department heads only the data that is relevant for them.