Referencing Filtered Cells

[Jorge]

Question:
When I use the auto filter and select an item in either column the second
row will always change row numbers. How can I reference the second row to
equal what ever shows when I filter an item?
Example…If I filter on column A for “SNRA14†I can’t reference it as (=A2)
because that will result in SNRA13.

Does someone have any suggestions>?

Thanks.
[7 Rows X 3 Columns below]


A B C
1 BOT TAM Type
2 SNRA13 SNRAH00 LD TRAM
3 SNRA13 SNRAH02 LD TRAM
4 SNRA14 SNRAH04 HD TRAM
5 SNAN01 SNANH25 LP TRAM
6 SNAN01 SNANH23 UD TRAM
7 SNAN55 SNANH09 UD TRAM

 

[Peo Sjoblom]

Might be easioer ways but here is one one way

=OFFSET($A$1,MATCH(1,SUBTOTAL(3,OFFSET($A$2,ROW($A$2:$A$100)-MIN(ROW($A$2:$A$100)),,)),0),)


entered with ctrl + shift & enter

adapt to fit your range

 

[Jorge]

Thank you Peo.

Works great....... Excellent work.

Might be easioer ways but here is one one way

=OFFSET($A$1,MATCH(1,SUBTOTAL(3,OFFSET($A$2,ROW($A$2:$A$100)-MIN(ROW($A$2:$A$100)),,)),0),)


entered with ctrl + shift & enter

adapt to fit your range


--
Regards,

Peo Sjoblom

Question:
When I use the auto filter and select an item in either column the second
row will always change row numbers. How can I reference the second row to
equal what ever shows when I filter an item?
Example.If I filter on column A for "SNRA14" I can't reference it as (=A2)
because that will result in SNRA13.

Does someone have any suggestions>?

Thanks.
[7 Rows X 3 Columns below]


A B C
1 BOT TAM Type
2 SNRA13 SNRAH00 LD TRAM
3 SNRA13 SNRAH02 LD TRAM
4 SNRA14 SNRAH04 HD TRAM
5 SNAN01 SNANH25 LP TRAM
6 SNAN01 SNANH23 UD TRAM
7 SNAN55 SNANH09 UD TRAM