Hi all,
Currently I have a formula in a sheet that calculates variance from a row in a Pivot table, such as B20 =VAR(B5:BL5). As additional data gets loaded into a data tab where the Pivot table reads in from (automatically refreshed by using ThisWorkbook.RefreshAll), how do I increment the number of columns each time to add new cells into this variance formula. For example, my current logic is L2 = 'NewDate', then B20 =VAR(B5:BM5). As NewDate gets updated, the last column in the variance function in B20 will move from BL to BM, BN, ... etc.
How do I do this? I tried to write a loop to increment the count but failed. Please advise on what's the most efficient way to do this, could be either in VBA or else. Much appreciated!
Stan
Currently I have a formula in a sheet that calculates variance from a row in a Pivot table, such as B20 =VAR(B5:BL5). As additional data gets loaded into a data tab where the Pivot table reads in from (automatically refreshed by using ThisWorkbook.RefreshAll), how do I increment the number of columns each time to add new cells into this variance formula. For example, my current logic is L2 = 'NewDate', then B20 =VAR(B5:BM5). As NewDate gets updated, the last column in the variance function in B20 will move from BL to BM, BN, ... etc.
How do I do this? I tried to write a loop to increment the count but failed. Please advise on what's the most efficient way to do this, could be either in VBA or else. Much appreciated!
Stan