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?
