SOLVED

Picker or filter on excel sheet

Copper Contributor

Hi Guys

 

I have a sheet full of job codes and would like to create a table that if I select a name, then only there codes would appear.  

The one thing i dont have is the list of owners on the actual sheet and as its not listed on report, i do have a hard copy, is there any thing i can do to store these on table on another tab and then have a picker or something? 

 

 

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

@Brendan_Wilson 

You can create a dynamic filter or picker in Excel to display job codes based on the selected owner's name, even if the list of owners is stored on another tab. Here is how you can do it:

Step 1: Create a List of Owners on Another Tab

  1. Go to another tab in your Excel workbook.
  2. Create a list of owners' names in a column. For example, you can have the owner names in column A of the new tab.

Step 2: Create a Data Validation Drop-down List for Owner Selection

  1. Go back to your original sheet where you want to display the filtered job codes.
  2. Select a cell where you want to create the owner selection drop-down.
  3. Go to the "Data" tab in the Excel ribbon.
  4. Click on "Data Validation" in the "Data Tools" group.
  5. In the "Data Validation" dialog box, under the "Settings" tab:
    • In the "Allow" drop-down, select "List."
    • In the "Source" field, enter the reference to the owner names you created on the other tab. For example, if the owner names are in column A of the second tab and you have, say, 10 owners, you can enter ='TabName'!A1:A10, replacing 'TabName' with the actual name of the tab where the owner names are.
  1. Click "OK" to create the drop-down list.

Step 3: Filter the Job Codes Based on the Selected Owner

  1. In the cell next to the owner selection drop-down, use Excel's filter feature (or the FILTER function if you are using Excel 365) to display the job codes corresponding to the selected owner.

For example, if your job codes are in column B and the owners are in column A, you can use a formula like this in cell C1:

=FILTER('JobCodeSheet'!B1:B100, 'JobCodeSheet'!A1:A100 = A1)

In this formula, 'JobCodeSheet' should be replaced with the name of the tab where your job codes are located.

Now, when you select an owner's name from the drop-down list, the job codes that belong to that owner will be displayed in column C.

Remember to adjust the cell references and column names according to your specific Excel file. The text and steps were created with the help of AI.

Attached in the attached file, two alternative proposals.

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark them as helpful!

This will help all forum participants.

Thank you :smiling_face_with_smiling_eyes:
1 best response

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

@Brendan_Wilson 

You can create a dynamic filter or picker in Excel to display job codes based on the selected owner's name, even if the list of owners is stored on another tab. Here is how you can do it:

Step 1: Create a List of Owners on Another Tab

  1. Go to another tab in your Excel workbook.
  2. Create a list of owners' names in a column. For example, you can have the owner names in column A of the new tab.

Step 2: Create a Data Validation Drop-down List for Owner Selection

  1. Go back to your original sheet where you want to display the filtered job codes.
  2. Select a cell where you want to create the owner selection drop-down.
  3. Go to the "Data" tab in the Excel ribbon.
  4. Click on "Data Validation" in the "Data Tools" group.
  5. In the "Data Validation" dialog box, under the "Settings" tab:
    • In the "Allow" drop-down, select "List."
    • In the "Source" field, enter the reference to the owner names you created on the other tab. For example, if the owner names are in column A of the second tab and you have, say, 10 owners, you can enter ='TabName'!A1:A10, replacing 'TabName' with the actual name of the tab where the owner names are.
  1. Click "OK" to create the drop-down list.

Step 3: Filter the Job Codes Based on the Selected Owner

  1. In the cell next to the owner selection drop-down, use Excel's filter feature (or the FILTER function if you are using Excel 365) to display the job codes corresponding to the selected owner.

For example, if your job codes are in column B and the owners are in column A, you can use a formula like this in cell C1:

=FILTER('JobCodeSheet'!B1:B100, 'JobCodeSheet'!A1:A100 = A1)

In this formula, 'JobCodeSheet' should be replaced with the name of the tab where your job codes are located.

Now, when you select an owner's name from the drop-down list, the job codes that belong to that owner will be displayed in column C.

Remember to adjust the cell references and column names according to your specific Excel file. The text and steps were created with the help of AI.

Attached in the attached file, two alternative proposals.

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark them as helpful!

This will help all forum participants.

View solution in original post