Fill formula without dragging down

[SaS012]

Hi how do I fill formula without having to drag down? I have added a formula next to pivot table. The total number of rows keeps changing each time i refresh the pivot
I am trying to find a way to have formula filled up automatically if the adjacent cell in the pivot is not blank

I have explored OFFSET and INDEX but cant really find a way to have this done. Please help

Example below, i am trying to add simple formula on Col E. But i need it to autofill if Col D is not blank

=C6/D7

 

[SanjayGMusafir]

I have added a formula next to pivot table

This thing doesn't work. If you need any kind of such result, better try Calculated Field option in Pivot Tables - That shall serve the purpose

Also, do I need to mention, their could be some limitations to what you can do in a calculated field.

 

[SaS012]

This thing doesn't work. If you need any kind of such result, better try Calculated Field option in Pivot Tables - That shall serve the purpose

Also, do I need to mention, their could be some limitations to what you can do in a calculated field.

I have values in my pivot table, i cant use calculated field. Even so, thats not the actual issue. Im looking for some way to auto fill formula without having to drag down.

 

[SanjayGMusafir]

I have values in my pivot table, i cant use calculated field. Even so, thats not the actual issue. Im looking for some way to auto fill formula without having to drag down.

See with pivot table you can use any auto-fill beside it. You told yourself that rows keep adding to pivot table after every refresh.

I wonder if there can be some way in VBA to calculate number of rows in a pivot table and auto filling the formula accordingly.

If your question reaches some VBA expert and (s)he knows the solution, would tell you. In process I shall also learn something new.

 

[SaS012]

I have tried this formula = C1:INDEX(C:C,COUNTA(C:C),1)/(D1:INDEX(D:D,COUNTA(D:D),1)
and it works until i have blank rows in in my data, then it stops calculating