Relative look up excluding hidden rows

[Intermediate]

Hi,

I think I'm just being dumb but I'm trying to calculate a value based on the row above eg.

A1... 100 =100
A2... =A1+1 =101
A3... =A2+1 =102

etc

However, I don't want to include hidden rows but if I hide row A2, A3 still reports 102 as the answer.
How do I make the formula dynamic so that A3 reports 101 if I hide row A2

Thanks

 

[Jerry Sullivan]

You might be able to use the SubTotal function to do that.

This thread has an example.
http://www.mrexcel.com/forum/showth...ic-index-predicting-use-auto-filter&p=2772662