Hi Guys,

Stuck on trying to get a consolidated list of completed tickets by tech name sorted largest to smallest. I have this sort of working using a pivot table, but then the table doesn't update when new data is entered. I've looked at refresh macros and sheet code to make this happen automatically but then I need to use an .xlsm file and the users need to know that they must enable macro content. It becomes messy. Is this the best way?

I feel like there might be an INDEX/MATCH formula that could do this. I'm looking to (dynamically?) populate column G in the attached example sheet.. that seems doable, but can it also be done in order of most completed tickets to least? Pivot table has sort options, so that's fine.. but I'd like to get away from the PT if at all possible. I guess I would also like to avoid sheet code to allow saving it as an xlsx file.

I say dynamically because I'd like to apply this to a much larger sheet that will have fluctuating data in it. To clarify, I'd like to be able to fill down the formula in column G to populate the list and then generate blank results once no more unique matches exist. Does that make sense?

I tried to use a dynamic range, I defined my range as 'TechCompleted' and set it as referrs to: =OFFSET($D$1,0,0,COUNTA($D:$d),1) but this seems to only capture a total number of cells in the dynamic range, and then that's what the pivot table looks for. It doesn't seem to be picking up the blanks, so people near the bottom of the list (column D) are getting missed. I also tried putting in =OFFSET($D$1,0,0,22,1) and while this gave me somewhat of the desired result, it still requires me to refresh the pivot table to pick up the changes. I thought using a dynamic range might force the PT to refresh, but I found that isn't happening either.

I'm attempting to use a formula in the cells in column G that avoids the need for the pivot table. If that's the only way, then I guess I can do that but I'd prefer to nix the PT to avoid the whole refresh issue. If the formula is in the cell (column G) then every time enter is pressed the people/counts in col G/H would update.

Would anyone be able to point me in a direction? I've been searching this for the past several days and I just can't seem to wrap my head around how to accomplish this. File is here: http://dl.dropbox.com/u/7974125/Misc/TechTotals.xlsx