How to pull multiple column data from another sheet

I am using the JOIN(COLLECT()) formula to pull data from another sheet. The issue is I can only pull from one column at a time. Is it possible to pull data from several columns using the JOIN(COLLECT()) referencing data from another sheet?

This is the current formula that I am using:

=JOIN(COLLECT({Roles/Access Range 6}, {Roles/Access Range 3}, [Department Cost Center]@row, {Roles/Access Range 1}, Position@row), ",")

I was wondering if the range "{Role/Access Range 6}" can be multiple columns.

Best Answer

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    Answer ✓

    Hello @RDRGSJ00

    On your source Sheet I would suggest adding a column and inputting your JOIN(COLLECT()) formula there so you can reference the multiple columns you want as one range. Then, you could use a simple lookup formula like INDEX(MATCH()) to pull this data in from that one column. Would that work?

    👨🏼‍💻 Dan Palenchar | School of Sheets Solutions Consulting (Smartsheet Aligned Gold Partner)

    If this response helped you please help me & the community by accepting it and reacting as you see fit (💡insightful, ⬆️ Vote Up, and/or ❤️Awesome).

    🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus

    ▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube

    School of Sheets (Smartsheet Partner)

    If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

Answers

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    Answer ✓

    Hello @RDRGSJ00

    On your source Sheet I would suggest adding a column and inputting your JOIN(COLLECT()) formula there so you can reference the multiple columns you want as one range. Then, you could use a simple lookup formula like INDEX(MATCH()) to pull this data in from that one column. Would that work?

    👨🏼‍💻 Dan Palenchar | School of Sheets Solutions Consulting (Smartsheet Aligned Gold Partner)

    If this response helped you please help me & the community by accepting it and reacting as you see fit (💡insightful, ⬆️ Vote Up, and/or ❤️Awesome).

    🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus

    ▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube

    School of Sheets (Smartsheet Partner)

    If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!