Sorting by Team

Hello Excelers ,

I need one of you to help me out with this one?

Attached is a small example of a larger spreadsheet, I print out for each team their areas, so the print area is from column B to column H. I need a formula in B1 to give me the Team name when I filter the list, for example if I sort by Team C, I want "Team C" to be displayed in B1 or sort by Team D, "Team D" to be displayed. Any ideas?

oldchippy

Oldchippy,

I am very happy to share with you a nice j-walk function ...

http://j-walk.com/ss/excel/usertips/tip044.htm

All the Best !!!

Carim

Thanks Carim,

Very impressed with your quick response - 3 minutes not bad!

Cheers

oldchippy

Thanks VBA Noob too

I knew you or Carim would be first to respond

All the best

It is "almost impossible" to beat VBA Noob, who is always complaining about how slow I am ...

Cheers
Carim

Ok Guys I need a little more help here with this VBA stuff

I find this like a different language and just can't get my head round it.

I've made use of the link you gave me and got the macro to work, but when I change the filter criteria to get cell B1 to update to the correct filtered team I have to click in the formula bar on the right of the formula and hit enter for the cell to update.

Is there a way.....no that's wrong. What is...the code I need to add so that when I alter the filter, cell B1 updates automatically.

Cheers

Who's going to be first?

OldChippy,

I personnally never had this problem ...

Are you using it in the cell B2 as is ...
or like
=MID(filtercriteria($A$10:$A$100),2,LEN(filtercriteria($A$10:$A$100))-1)
to remove the sign =

Anyways in both cases, it should be OK ...
Let me try to replicate your problem ...

Carim

A ridiculous question ...
Tools Option Calculation ...

Originally Posted by Carim

OldChippy,

I personnally never had this problem ...

Are you using it in the cell B2 as is ...
or like
=MID(filtercriteria($A$10:$A$100),2,LEN(filtercriteria($A$10:$A$100))-1)
to remove the sign =

Anyways in both cases, it should be OK ...
Let me try to replicate your problem ...

Carim

Silly me

I had =FilterCriteria(A3)

Thanks again - sorted now - excuse the pun

Glad your problem is fixed ...

It happens to all of us ...

Cheers
Carim