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.

	A	B
1
2		REFERENCE
3	UNHIDDEN	1
4	HIDDEN
5	HIDDEN
6	UNHIDDEN	2
7	UNHIDDEN	3
8	HIDDEN
9	UNHIDDEN	4

<TBODY>
</TBODY>

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.