Referencing Filtered Cells
 

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 cant 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

Referencing Filtered Cells
 

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


"Jorge" wrote in message
...
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

Referencing Filtered Cells
 

Thank you Peo.

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


"Peo Sjoblom" wrote:

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


"Jorge" wrote in message
...
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