SOLVED

# Create list from other sheet

Copper Contributor

# Create list from other sheet

I'm sure this has been asked before, but I'm unsure what to look for.  I have a spreadsheet that I'm using for a dashboard.  I manage several support cases and use multiple criteria to see what needs to be focused on.  I have manual processes in place to pull this data.  However, depending on the criteria, I would like to have a list created automatically from a table. The place that this list would go needs to auto-fill with no spaces, and I can't hide any of the rows.  The Case # field under the escalations is one of the places that I would like to do this.  Also, the "Poachable" cases.  Is this something that can be done?

2 Replies
best response confirmed by Eegeelabs1360 (Copper Contributor)
Solution

# Re: Create list from other sheet

It’s possible to create a dynamic list based on specific criteria in Excel. If you want to automatically generate a list based on criteria and have it update dynamically, you can use Excel functions like FILTER or IF combined with INDEX and MATCH.

Here is a basic guide on how you might achieve this:

Using FILTER Function (for Office 365 / Excel 2019):

Assuming your data is in a structured table, you can use the FILTER function to create a dynamic list based on criteria.

1. Create a Criteria Table:
• Create a small table with the criteria you want. For example, you can have a column for "Escalations" and another for "Poachable."
2. Use FILTER:
• In the cell where you want the dynamic list, you can use a formula like this:

=FILTER(Table1, (Table1[Escalations]="Yes") * (Table1[Poachable]="Yes"))

Using IF, INDEX, and MATCH (for Older Versions):

If you don't have access to the FILTER function, you can use a combination of IF, INDEX, and MATCH functions.

1. Create a Helper Column:

• Add a helper column next to your data and use an IF formula to mark the rows that meet your criteria.

=IF((A2="Yes")*(B2="Yes"), 1, 0)

• Assuming "Escalations" is in column A and "Poachable" is in column B.

2. Create a Dynamic List:

• In the cell where you want the dynamic list, use an INDEX and MATCH formula.

=INDEX(A:A, SMALL(IF(\$C\$2:\$C\$100=1, ROW(\$C\$2:\$C\$100)-MIN(ROW(\$C\$2:\$C\$100))+1, ""), ROW(1:1)))

This formula creates a dynamic list that adjusts as you add or remove data. Drag this formula down to populate the list.

Remember to replace "Table1," "Escalations," "Poachable," and the column and row references according to your actual data.

The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

# Re: Create list from other sheet

Thank you! The only thing I could find in google was creating a dyna mic dropdown list.
1 best response

Accepted Solutions
best response confirmed by Eegeelabs1360 (Copper Contributor)
Solution

# Re: Create list from other sheet

It’s possible to create a dynamic list based on specific criteria in Excel. If you want to automatically generate a list based on criteria and have it update dynamically, you can use Excel functions like FILTER or IF combined with INDEX and MATCH.

Here is a basic guide on how you might achieve this:

Using FILTER Function (for Office 365 / Excel 2019):

Assuming your data is in a structured table, you can use the FILTER function to create a dynamic list based on criteria.

1. Create a Criteria Table:
• Create a small table with the criteria you want. For example, you can have a column for "Escalations" and another for "Poachable."
2. Use FILTER:
• In the cell where you want the dynamic list, you can use a formula like this:

=FILTER(Table1, (Table1[Escalations]="Yes") * (Table1[Poachable]="Yes"))

Using IF, INDEX, and MATCH (for Older Versions):

If you don't have access to the FILTER function, you can use a combination of IF, INDEX, and MATCH functions.

1. Create a Helper Column:

• Add a helper column next to your data and use an IF formula to mark the rows that meet your criteria.

=IF((A2="Yes")*(B2="Yes"), 1, 0)

• Assuming "Escalations" is in column A and "Poachable" is in column B.

2. Create a Dynamic List:

• In the cell where you want the dynamic list, use an INDEX and MATCH formula.

=INDEX(A:A, SMALL(IF(\$C\$2:\$C\$100=1, ROW(\$C\$2:\$C\$100)-MIN(ROW(\$C\$2:\$C\$100))+1, ""), ROW(1:1)))

This formula creates a dynamic list that adjusts as you add or remove data. Drag this formula down to populate the list.

Remember to replace "Table1," "Escalations," "Poachable," and the column and row references according to your actual data.

The text and steps were edited with the help of AI.