EXCEL 2010 - Referencing cell contents of last unhidden cell

[NPetersen]

Hello,

I have a column ("REFERENCE") which is intended to display row numbers in order (ie. 1, 2, 3, 4, etc...). I would like to write a formula that returns the value of the cell above +1. The column contains a number of hidden rows throughout the range and I would like the formula to look for the previous "unhidden" cell.

Additionally, the rows which are hidden will change from time to time and I would like this formula to keep that list of row numbers updated based on what is hidden/unhidden. What formula should I be using?

The table looks something like this... although the formula would be entered into every cell within B3:B9, the values I've entered in the "REFERENCE" column are the desired result.

[TABLE="class: grid, width: 500, align: center"]
<TBODY>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD]REFERENCE
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]UNHIDDEN
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]HIDDEN
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]HIDDEN
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]UNHIDDEN
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]UNHIDDEN
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]HIDDEN
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]UNHIDDEN
[/TD]
[TD]4
[/TD]
[/TR]
</TBODY>[/TABLE]


Thanks for the help.

N

 

[Andrew Poulsom]

Does this formula work for you in B3 copied down?

=SUBTOTAL(103,A$3:A3)

 

[NPetersen]

Does this formula work for you in B3 copied down?

=SUBTOTAL(103,A$3:A3)

Hi Andrew,

I didn't actually have the text "Hidden/Unhidden" in column A, but I do have text in every row of column C which I can use for this. After adapting the formula to look at C rather than A, this works great!
Out of curiosity, do you know how I could alter the formula if I wanted the same result but could not guarantee that each row of column C contained something?

Thanks!

-N

 

[Andrew Poulsom]

Out of curiosity, do you know how I could alter the formula if I wanted the same result but could not guarantee that each row of column C contained something?

No, sorry.