have formula update with filter?

I'm hoping somebody here knows more about excel than I do, sorry to post technical questions not related to admissions, but I could really use some help here.

I have a very large data sets that I'm working with in excel 2007 and I have to continually filter and unfilter the data by groups. I want to know if there's a way that I can have the values from my formula update themselves each time I filter/unfilter the data.

Suppose I have a table like this:
Group | Age | Rank | Gender
A 15 1 M
A 5 2 F
A 1 3 F
B 12 1 M
B 3 2 M
B 2 3 F


Where there are two groups, A and B, and each person has an age, and then the third column we have a ranking. This ranking in third column is computed by sorting the data by age and then by group, and then we set the first entry to 1 and then every other entry has an "if" equation where if the group is the same as the previous column then we add 1 to the ranking, and if not then we set it equal to 1.

Now say that just as a thought experiment we want to only look at females and have the rankings for females. If you do a basic filter and select only show girls what you get is:

Group | Age | Rank | Gender
A 5 2 F
A 1 3 F
B 2 3 F

However, what I want is:

Group | Age | Rank | Gender
A 5 1 F
A 1 1 F
B 2 2 F

Is there anyway to make the formula update like this, so it treats what we see as the first row of data as if it were actually the first row, instead of still the second row with the first row "invisible" but existent?

I know it's easy to do this in a one time thing through an advanced filter or simply deleting the rows I don't want; but I need to do literally hundreds of filters and don't want to delete anything. For example right after I look at only the female data I may want to do the same thing only looking at just the male data. Is there any easy way to do this?

I know you can have macro's work off relative position instead of absolute, is there anyway for formulas/filters to do this?

Thanks in advance!

The key question I think is whether or not you're filtering by anything other than Gender - I suspect this is a much dumbed down illustration ...

I would honestly say that VBA is likely to be your best bet purely because if there are multiple filters things could get quite awkward... unfortunately there is no COUNTIF function available within the SUBTOTAL function and using a SUMPRODUCT with SUBTOTAL & OFFSET is pretty much guaranteed to bring your file to a halt... the key I feel will be determining which are the filters in place... ascertaining that even with VBA can be a pain at times.

Here is one possible cheat based on the simple example... if we assume data is in A1:D7 with row 1 being headers... then

Please Login or Register  to view this content.

Using the above, were you to alter visibility of Gender or Age via Filters you should find Ranks in C update accordingly (again based on assertion that data is sorted by Group & Age (Desc))

As to how practical the above would prove to be with large and more complex sets of data I don't know...

Sounds like a plan.

In hindsight - having now thought some more - it would I think be a little better to alter the below

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

The above assumes Group names (A2 etc) to be Text by nature (not numbers - ie A, B rather than 1,2) -- this revised approach then enables you to alter the RANK formula approach from:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Which should be a little more efficient I think... generally speaking the LOOKUP(2,1/....) approach is quite slow, by altering E so as to return only Text values for visible rows (0 for hidden rows) we can use a slightly different approach given the visible rows no longer contain text in E (ie null in prior version which in effect is 0 len text string)