Select active column reference in a formula

Question

My array formula searches for a value in a certain column (V:V), and based on that, displays a list found in column C.  I need to use this same formula but on different columns. How can I force the formula to use the active column which I have selected?

=IF(COUNTIF(V:V,$BT$7) < ROWS($BU$7:BU7), "",  INDEX(C:C,  SMALL( IF($V$7:$V$34 =$BT$7, ROW( $V$7:$V$34)), ROW(C1))))

Answer 1

Determining the column associated with the ActiveCell would require a VBA macro. Perhaps something like,

Sub MyListWhereIwantIt()

ActiveCell.Resize(29, 1).FormulaArray = _

"=IF(COUNTIF(V:V,$BT$7)< ROWS($BU$7:BU7), """"," & _

"INDEX(C:C,SMALL( IF($V$7:$V$34=$BT$7,ROW($V$7:$V$34)),ROW(C1))))"

End Sub

It wasn't clear whether you wanted that formula entered into an array of cells or as an array formula in a single cell then filled down. I suspect the latter due to the use of ROWS() rather than ROW(). Could you provide specifics on what the formula is intended to accomplish?

Answer 2

You can't. Formulas can not reference the current selection for a number of very good reasons. What you could do would be to use indirect and reference a cell where you are storing the column you want to reference. So for instance put a V in cell A1 and change your current formula to reference the value in A1 as part of an indirect. A bit messy and indirect is volatile so you will take a performance hit but it will work.