Trying to pull unique values from another sheet's column

Options
Faaez Kamaal
Faaez Kamaal ✭✭✭
edited 03/16/21 in Formulas and Functions

Just as the title says, I'm trying to build a solution that parses and lists the "Region Name" field in a sheet, so it can build another list based on "Regional Partner". Basically, I'm trying to build an abstract method of building a list of regions and their associated regional partners. Why? Because these 2 fields seems to change more frequently than I'd like to admit.

Anyway, through some searching, I found Paul's solution; https://app.smartsheet.com/b/publish?EQBCT=416b5082452c49029af303c13250f7da

Problem is, the data isn't in comma delimited cells. It's 20+ rows of the same name.

I'm trying to pull from "Office Directory - 20210223" into "New Chair Installs (Metrics)". I should probably break this off into it's own sheet.

The above shows what I'm trying to achieve, and the below is what I've tried.

I'm at my wits end.

EDIT: I know I'm not the best of explainers. If I can just pull the "Region Name" dynamically, I can use INDEX/MATCH formulas to pull the rest of the info I need.

Best Answer

  • Faaez Kamaal
    Faaez Kamaal ✭✭✭
    edited 03/17/21 Answer βœ“

    Paul, I realized something last night. I need the raw data in the same sheet. It seems that there is no way for me to reference a single cell from another sheet. EG: "={SomeData}1" I think that was my hinderance to this whole process.

    When I tried "=IFERROR(LEFT(String@row, FIND(",", String@row) - 1), "")" with "{SomeData}", I got "#INVALID DATA TYPE". And... I think I cracked the code. I need to have all the columns on the sheet and ONLY reference {SomeData} in the "String 1" Column. I'll report back.


    EDIT: Never Smartsheet with no coffee and while sleep deprived. I figured it out with that other sheet. Significantly cleaner, and I can reference my other sheet.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!