I was trying to do the R1c1 rotation in the excel. My question is how can we use the R1c1 on the visible cells in the Excel avoiding the hidden rows

[Goverdhan]

For Example, consider In excel you have 10 rows and you hid 5 rows in excel. I want the R1c1 rotation formula that calculates base on the visible cells in excel. that look like R[-4]C-R[-5]C. It is calculating the hidden rows also.


Any Help really appreciated.

 

[maabadi]

Welcome to MrExcel Messeage Board.
Please tell your formula and show an Example.

 

[maabadi]

I think this is worked for you:

=INDIRECT("E"&ROW()+5-SUBTOTAL(103,$E$5:$E$13),TRUE)-INDIRECT("E"&ROW()+4-SUBTOTAL(103,$E$5:$E$13),TRUE)

$E$5:$E$13 is Total Column Range
And formula is after the Last cell (here E14)

 

[maabadi]

I changed formula to this:

=INDIRECT("E"&SUBTOTAL(103,$E$5:$E$13)+1-SUBTOTAL(103,$E$5:$E$8)+SUBTOTAL(3,$E$5:$E$8),TRUE)-INDIRECT("E"&SUBTOTAL(103,$E$5:$E$13)-SUBTOTAL(103,$E$5:$E$8)+SUBTOTAL(3,$E$5:$E$8),TRUE)

1. $E$5:$E$13 is Total Column Range
2..And formula is after the Last cell (here E14)

3. You should change also all of $E$8 to your formula address -6 : Here formula adress is E14 then I put $E$8 .