Hey Guys!

I immensely need your help for the below scenario.

I am trying to find a way to address range in a formula. The problem is that, the range is created dynamically. Please refer below image further.

Sketch1.jpg

Here I have 2 tables say Table 1 & Table 2, these tables are snippets of my actual table which has 50 columns & 40 Rows.

Table 1 is generated from a pivot table, and it is dynamic so I wont be having idea of its address (except cell A1 where table 1 starts always)

so Have used the below codes to set range of table 2

Set table2top = Range("A1").End(xlToRight).Offset(0, 1) 
Set table2bot = Range("A1").End(xlToRight).End(xlDown).Offset(0, 1)
Now i have to enter formula =AVERAGEIF($A$1:$I$1,K$1,$A2:$I2) in cell K2 and use autofill for rest of table 2.


I need your help to Replace cell range ($A$1:$I$1,K$1,$A2:$I2) to 'something' as i wont be knowing cell range except starting cell A1.


P.S. I tried naming date row. so i can replace $A$1:$I$1 with a name, but could not figure out for the next part of formula.

I just started using VBA to build a model & Im almost halfway through my model but stuck at this point please help me out