Drop Down List

[Lee]

Is it possible to link an item from a drop down list that completes another action, for example.
I select a title from a drop down list in B2, Would it be possible to select automate and reference the drop down result so that you can generate an automated reaction. What I want to do is when selecting a title from the drop down it moves that title to say B7 and, the relevent information below the title to the top of the page to be reviewed, I would then undo the action to return the full list of items. I hope this makes more sense to you than me I am trying to achieve it without VB because it is a protected sheet and they will not allow VB on the sheet to be active.

Thanks
Lee

 

[shamimarita]

Hello Lee,

What you're describing is selecting from a drop-down in B2 to automatically move the title to B7 + pull its related info (below it) to the top, with easy undo, and no VBA (due to protection), which isn't directly possible with formulas alone. True "moving" or rearranging rows on selection requires VBA (Worksheet_Change event) or macros, which won't work here.

Closest non-VBA solutions (using formulas only, works on protected sheets if cells are unlocked):

1. Pull/display selected info at top (recommended – no actual move)
Keep the full list intact (easy undo: just change/clear B2). Show the selected title + details in a fixed review area (e.g. B7 downward).

• B2: Data Validation > List (your titles source)
• B7 (review title):

=IF(B2="","", B2)

• B8 onward (related info): Use XLOOKUP (Excel 365/2021+) or INDEX/MATCH

Example for one detail column:
=IF(B2="","", XLOOKUP(B2, [titles range], [detail range], "Not found"))

Repeat for each related column/row.
If related info is a multi-row block per title, use FILTER (Excel 365):
In B7 (spills down):
=IF(B2="","", FILTER([full data range], [title column]=B2, "Select title"))

Pros: Automatic, dynamic, no code, original list stays for "undo".

2. If you need visual "shortening" of the list:
Add a helper column flagging selected items (e.g. via formula), then manually apply a Filter to hide them. Not automatic, but no VBA.

If your Excel version lacks XLOOKUP/FILTER (older than 365/2021), use INDEX/MATCH or VLOOKUP equivalents.

 

[Lee]

Hello Lee,

What you're describing is selecting from a drop-down in B2 to automatically move the title to B7 + pull its related info (below it) to the top, with easy undo, and no VBA (due to protection), which isn't directly possible with formulas alone. True "moving" or rearranging rows on selection requires VBA (Worksheet_Change event) or macros, which won't work here.

Closest non-VBA solutions (using formulas only, works on protected sheets if cells are unlocked):

1. Pull/display selected info at top (recommended – no actual move)
Keep the full list intact (easy undo: just change/clear B2). Show the selected title + details in a fixed review area (e.g. B7 downward).

• B2: Data Validation > List (your titles source)
• B7 (review title):

=IF(B2="","", B2)

• B8 onward (related info): Use XLOOKUP (Excel 365/2021+) or INDEX/MATCH

Example for one detail column:
=IF(B2="","", XLOOKUP(B2, [titles range], [detail range], "Not found"))

Repeat for each related column/row.
If related info is a multi-row block per title, use FILTER (Excel 365):
In B7 (spills down):
=IF(B2="","", FILTER([full data range], [title column]=B2, "Select title"))

Pros: Automatic, dynamic, no code, original list stays for "undo".

2. If you need visual "shortening" of the list:
Add a helper column flagging selected items (e.g. via formula), then manually apply a Filter to hide them. Not automatic, but no VBA.

If your Excel version lacks XLOOKUP/FILTER (older than 365/2021), use INDEX/MATCH or VLOOKUP equivalents.

Thank you I will try and give this a try..

 

[shamimarita]

You are most welcome. Let us know your feedback!