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.
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
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
(P.S. Also posted on http://www.vbaexpress.com/forum/sho...nges-(without-range-address)-inside-a-Formula )
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.
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
Code:
[COLOR=#333333]Set table2top = Range("A1").End(xlToRight).Offset(0, 1)
Set table2bot = Range("A1").End(xlToRight).End(xlDown).Offset(0, 1)
[/COLOR]
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
(P.S. Also posted on http://www.vbaexpress.com/forum/sho...nges-(without-range-address)-inside-a-Formula )