If row is hidden than the hidden row is not referenced

[glide2131]

Hello, I have a list that I want to reference. I want the reference to only show the rows that are not hidden with no blanks.

For example, I hid row 4 and I would like the cell references in G13:H17 to not reflect row 4. I don't want the same data to be hidden in both spots and I don't want a row that is blank. I simply don't want it to show up at all in the reference and in the same order.

Sheet 2 is what I would like the results to look like if I were to hide just row 4.

In addition, if I were to unhide row 4 I would like row 4 to reappear in the referenced cells.

Does anyone know a way to do this?

Thanks

[Glenn Kennedy]

1. Insert a row and apply a header row.

2. Filter values out using the header row.

3. Use this formula, copied across and down:


=IFERROR(INDEX(A:A,AGGREGATE(15,6,1/(1/(SUBTOTAL(3,OFFSET($A$2,ROW($A$2:$A$7)-ROW($A$2),))*(ROW($A$2:$A$7)))),ROWS(G$13:G13))),"")

[glide2131]

This is great! Thank you. Much appreciated!

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.