Community: How to get information from Sheet A to Sheet B based on dropdown selection in Sheet B?

first

In this question, Sheet A contains job titles of different departments (Dept IDs) and if a particular department ID is selected in the dropdown list of Sheet B, how to pull out all job titles from Sheet A? There are questions about.As an answer, jmyzk_cloudsmart_jp suggests using the COLLECT function.

Question summary

If Sheet A contains job titles of different departments (Dept IDs) and a particular department ID is selected in the dropdown list of Sheet B, how to pull out all job titles from Sheet A?

Answer summary

Using the COLLECT function proposed by jmyzk_cloudsmart_jp, a method is introduced to obtain the job title from the title range of Sheet A, with the Department ID range of Sheet A as the reference range, and the [Department ID]@row of Sheet B as the reference range. I am.As a concrete example, the following formula is provided:

=JOIN(COLLECT({Job Title}, {Department ID}, [Department ID]@row), ", ")

翻 訳

RDRGSJ00 ✭

09/09/23 Edit 09/09/23

There are two separate sheets.Sheet A contains job titles (department IDs) for various departments.If a specific department ID is selected in the drop-down list in Sheet B, I need to retrieve all job titles from Sheet A.Any information would be greatly appreciated.

best answer

  • RDRGSJ00 ✭
  • 2023/09/21 Answer ✓
  • Thank you, jmyzk_cloudsmart_jp.done.I was able to pull information from one column based on department ID and job title. Is it possible to pull multiple columns instead of just one?
  • answer
  • jmyzk_cloudsmart_jp ✭✭✭✭✭✭
  • 09/10/23
  • Hello@RDRGSJ00
    COLLECT functionto get the title from the title range of sheet A that includes the department ID of sheet A. Use range as the reference range and [Department ID]@row in Sheet B as the reference range. 😁
    Example:
    =JOIN(COLLECT({Job Title}, {Department ID}, [Department ID]@row), “, “)
    Check out the demo dashboard below.
    You can edit the Department Name: ID drop-down list on Sheet A and add new data to Sheet B to see how the formula works.
  • smart seat
  • https://app.smartsheet.com/b/publish?EQBCT=f2df029f734b4727a7f789b311b44fdb
  • image.png
  • RDRGSJ00 ✭
  • 2023/09/21 Answer ✓
  • Thank you, jmyzk_cloudsmart_jp.done.I was able to pull information from one column based on department ID and job title. Is it possible to pull multiple columns instead of just one?

Summary

As suggested by jmyzk_cloudsmart_jp, you can effectively pull the job title from Sheet A if a specific Dept ID is selected in Sheet B's dropdown list.Try using the demo dashboard link provided to see how the formula works in practice.

List of key points

  1. Sheet A contains job titles for different departments (Dept IDs).
  2. You can select a specific department ID from the Sheet B drop-down list.
  3. jmyzk_cloudsmart_jp suggests using the COLLECT function to extract the job title from Sheet A.
  4. The formula provided is=JOIN(COLLECT({Job Title}, {Department ID}, [Department ID]@row), ", ").
  5. A demo dashboard link is provided for practical testing.

Leave a comment