Forum Discussion
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?
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.
- 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."
- 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"))
- Adjust the criteria according to your needs.
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)))
- Adjust the column references according to your data.
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.
If these steps not helped you, I recommend adding more information to your topic. Information such as Excel version, operating system, storage medium, file extension, etc. In this link you will find some more information about it: Welcome to your Excel discussion space!
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
2 Replies
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.
- 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."
- 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"))
- Adjust the criteria according to your needs.
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)))
- Adjust the column references according to your data.
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.
If these steps not helped you, I recommend adding more information to your topic. Information such as Excel version, operating system, storage medium, file extension, etc. In this link you will find some more information about it: Welcome to your Excel discussion space!
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
- Thank you! The only thing I could find in google was creating a dyna mic dropdown list.
