Formula linked to dynamic range of spilled range

[ajocius1]

Hi, I have UNIQUE formula returning relevant records from data source table. Result is obviously dynamic depending on source data. I have list of unique vendor names and added formulas to count orders that looks for vendor name in result of Unique formula and then using CountA formula. List of unique vendor names will change over time, list will either reduce or get longer. I would like to match my count formula to the dynamic list of vendors. Any way to do it smart instead of verifying formulas I get new data?

 

[Fluff]

You can refer to the 1st cell in a spill range like A2#

 

[ajocius1]

Book1
	A	B
1	Name	Count
2	John	5
3	Steve	4
4	Joshua	6
5		7
Sheet1

Here is example of what I try to do. List in column A is result of UNIQUE formula and has spill range. It is dynamic, so when more data is loaded it will expand. I have then COUNTA formula in column B. I would like column B to be also dynamic, linked to number of records in column A

 

[ajocius1]

Tried to get around with VBA code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$2" Then
            Range("B6:B99999").Clear
            Range("B6:B" & A4 + 5).Formula2R1C1 = "=COUNTA(FILTER(Table2[Name],Table2[Name]=RC[-1]))"
    End If
End Sub

My worksheet has formula, so the value of that cell with formula changes, however it does not trigger above macro

 

[ajocius1]

Tried to get around with VBA code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$2" Then
            Range("B6:B99999").Clear
            Range("B6:B" & A4 + 5).Formula2R1C1 = "=COUNTA(FILTER(Table2[Name],Table2[Name]=RC[-1]))"
    End If
End Sub

My worksheet has formula, so the value of that cell with formula changes, however it does not trigger above macro

Solved by moving above macro to the sheet where I actually make a change, which triggers formula calculation, so now it works. However, if there is smart way of achieving this without macro, would be great to hear.

 

[Fluff]

You just need to refer to the spill range as I showed in post#2

=COUNTIFS(Table2[Name],A2#)