Home

Dynamic Dropdown List with Moving Target Column

sungyang
Occasional Visitor

I am looking to create a dynamic drop down menu that requires referencing a moving column.

 

I can create a simple dynamic drop down menu using the below formula:

 =OFFSET ( ReferenceDEFINEDColumnHeader, 1, 0, COUNTA(RangeBelowDEFINEDColumn)-1)

so something like

 = OFFSET (F1,1,0,COUNTA(F:F)-1)

 

This is obviously happening all within data validation.

 

However, if my column changes based on some input (thus not always F), then I cannot use the above.

 

I have attempted to use some variation of OFFSET / MATCH but seems not to be working. I've also attempted to think through a INDEX MATCH formula but have failed at it as well.

 

Hoping someone has attempted this type of formula before that can help me out.

 

Also, the list source is on a separate worksheet though I doubt that makes it any better or worse.

 

Thanks.

 

------------------------------

 

Update -- I have managed to create the below to get me super close:

 

= OFFSET (FirstCellofReferenceHeaderRow, 1, MATCH(InputReferenceCell, ReferenceHeaderRow)-1, Reference Height)

 

This allows the validation to move dynamically across the various columns. However, I need the Reference Height to be dynamic. I seem to not be able to figure out a good formula for referencing the very bottom of the entire list reference I am looking into where I have calculated the height using a simple "CountA" statement.

 

Get me over the hump? Anyone?

1 Reply
I believe I can construct the formula for you if you attach your sample file with given data and expected results. Certainly, I won’t be using OFFSET for being volatile. I will be using INDEX, the most versatile function from my viewpoint.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies