Drop down selection

[Lee]

I have created a drop down list from a selection of titles in a project sheet. What I want to do is select a project title from the drop down and that project title appears at the top of the page so that it can be reviewed. The sheet is in excel. I hope that makes sense any help most welcome.

Thanks
Lee

 

[shamimarita]

Hello Lee,

If your drop-down list is created using Data Validation, the easiest way is simply to reference the cell that contains the drop-down.

Example:
1. Suppose your drop-down list is in cell B2.
2. Go to the cell at the top of the page where you want the selected project title to appear (for example A1).
3. Enter this formula:
=B2
Now, whenever you select a project title from the drop-down in B2, the same title will automatically appear in A1.

Optional (if you want it to look like a heading)

You can:

• Increase the font size
• Make it bold
• Merge cells across the top for a cleaner title display

If your setup is slightly different (for example, the drop-down is on another sheet), you can still reference it like this:
=Sheet2!B2

 

[Lee]

Hi.
Thanks for your respons. Could I explain a little more please. I have attached a sample of the sheet to better explain my intention. In the top of the sheet I have a drop down created by a validation list. When I select a title which is highlighted in the sheet I would like the title to appear ate the top of the page. The original list is a lot longer and takes a lot of scanning through to get to the project manually. Hope that make sense.

Thanks again
Lee

 

[shamimarita]

Hello Lee,

Thanks for sharing the sample sheet. I can see that your TASK NAME list is in column D, and you want to select a task from a drop-down and show that selected item at the top of the sheet.

You can do this with a Data Validation drop-down + XLOOKUP.

Step 1: Create the Drop-Down

• Select a cell where you want the selector (for example B1).
• Go to Data → Data Validation → List.
• Set the Source to your task list, for example:

=D5100
(Adjust the range to cover all tasks.)

Step 2: Return the Selected Task at the Top

In the cell where you want the selected task displayed (for example D2), use:
=XLOOKUP(B1,D,D)
This will display the selected TASK NAME from the list.

Optional: Return the Whole Row
If later you want to show Priority, Code, or other columns for the selected task, you can use the same approach, for example:
=XLOOKUP($B$1,$D:$D,$C:$C)
This returns the corresponding value from another column in the same row.

Tip: You may also want to convert the task list to an Excel Table (Ctrl + T) so the drop-down automatically expands when new tasks are added.

 

[Lee]

Hello Lee,

Thanks for sharing the sample sheet. I can see that your TASK NAME list is in column D, and you want to select a task from a drop-down and show that selected item at the top of the sheet.

You can do this with a Data Validation drop-down + XLOOKUP.

Step 1: Create the Drop-Down

• Select a cell where you want the selector (for example B1).
• Go to Data → Data Validation → List.
• Set the Source to your task list, for example:

=D5100
(Adjust the range to cover all tasks.)

Step 2: Return the Selected Task at the Top

In the cell where you want the selected task displayed (for example D2), use:
=XLOOKUP(B1,D,D)
This will display the selected TASK NAME from the list.

Optional: Return the Whole Row
If later you want to show Priority, Code, or other columns for the selected task, you can use the same approach, for example:
=XLOOKUP($B$1,$D:$D,$C:$C)
This returns the corresponding value from another column in the same row.

Tip: You may also want to convert the task list to an Excel Table (Ctrl + T) so the drop-down automatically expands when new tasks are added.

Thank you so much I will try this. Another thought was is it possible to link this action with a automation rule? the only thing is how would you combine the drop down list with the rule? Just a thought...

 

[Lee]

Hi.
I did get a solution but there is one problem with it. I have attached a section of the sheet for your attention. I have a selection process for the project title, from that result I use an XLOOKUP to fill cells to the right, the problem is the filtered data does not show the last line of information in the XLOOKUP results, could you offer some help with this please. It does not show the data from the original list, this is the same for all the projects listed.


Projects Data Sheets New 2025.xlsm

 

[shamimarita]

Hello Lee,

It sounds like the issue is coming from how XLOOKUP behaves with filtered data. By default, XLOOKUP does not ignore filtered (hidden) rows, so it may return a value that isn’t currently visible, or miss what you expect as the “last visible” result.

If your goal is to return the last visible match from filtered data, XLOOKUP alone won’t do the job correctly. You can try one of these approaches:

Option 1: Use FILTER + INDEX (for visible results)

If you’re on Excel 365/2021:
=INDEX(FILTER(ReturnRange, (LookupRange=LookupValue)*(SUBTOTAL(103,OFFSET(LookupRange,ROW(LookupRange)-MIN(ROW(LookupRange)),0)))),ROWS(FILTER(...)))

This ensures only visible (filtered) rows are considered.

Option 2: Use AGGREGATE (more stable for filtered lists)

You can identify the last visible row using AGGREGATE and then return the value accordingly.

Option 3: Helper Column (simpler approach)

Add a helper column with:

=SUBTOTAL(103, A2)

Then use XLOOKUP or another formula based only on rows where this helper column = 1 (visible rows).