Need a little help with "totals"

[Tommy]

Hi All,

I have a spreadsheet attached with what I am working with. What I would like to do is select the County and have it appear at the bottom with the rainfall intensity. This is a lot smaller than what I work with but it still works the same. I have over 3100 lines so I cut them down a little.
I have it set up to copy the bottom line with the rainfall intensity to another page and it helps design the gutter and downspouts to carry the water off. This is working just fine, I am using subtotals. The part I need the help with is when I select the county I would like to have it show up right beside the rainfall intensity, this way I can copy it to another sheet and finish the documentation. I would like a formula if at all possible. I can code the VBA no problem, I am thinking that I don't have to, this would be the only reason to have any VBA at all and seems such a waste. I just don't know the formulas I need to pick up only the visible cells.

This is to keep me from having to type so much. I have a real bad case of fat fingers. :)

[SamT]

The formula for the Cell you want the selected county name to appear in

=fSelectedCounty()

The Module1 code

Option Explicit

Function fSelectedCounty() As String
Dim RFISht As Worksheet
Dim Result As String
Set RFISht = Sheets("Rain Fall Intensity")

If RFISht.AutoFilterMode Then
With RFISht

If .AutoFilter.Filters(1).On Then
Result = .AutoFilter.Filters(2).Criteria1
fSelectedCounty = Right(Result, Len(Result) - 1)

Else
fSelectedCounty = ""
End If
End With
Else
fSelectedCounty = ""
End If
End Function

[Tommy]

Hi SamT,

I changed the function to a public function and I had to change
If .AutoFilter.Filters(1).On Then to
If .AutoFilter.Filters(2).On Then
and it worked just fine.

So I guess there is no formula to do this with then right?

Thanks

[SamT]

I'm using XL XP so the Public makes sense.

I first tried Filters(2) and had to change it to Filters(1) ?!?!?!

I'm glad you were able to see the need to change it back.

[Tommy]

I went from Win XP to Win 7 and Office 2003 to 2013 what a jump! Stopped me in my tracks for about 3 days and it has been slow going every since.

I am thinking that since we set Filters 2 and 3, that is why there is a difference. We do not set Filters on 1. But then again I am guessing. :)