Extracting Records from an Excel Table based on a Cell Value

[Excelanil]

Hello Everyone/Anyone:

I’m looking for some help in developing an Excel Formula that will list or extract for me all the records associated with a Sales Rep from a table. I am not interested in a Macro. Also, I do not want to use a filtering method because I may want to add certain calculations for certain fields in the records, say for example, “the age of an opened opportunity” during the exttraction process.</SPAN></SPAN>

Here is my situation:</SPAN></SPAN>
The Table consists of a Range defined as Table, $B$2:$J$1000. It could be a bigger Range in the future as changes occur. I consider each Row in the Table a record with the Sales Rep names listed in Colum B. The Table have Header Lables. Now, I want to enter a Sales Rep name in Cell A1 and as a result pull all the records associated with the Sales Reps named in Cell A1, including his name across the right side of the table or onto another Sheet if need be. </SPAN></SPAN>

I do not mind copying down the formula in each column where the listing will occur; but if there are no more records for the Sales Rep, I do not want to see any error indications. In other words, I’d like to see some kind of “IFERROR” or this sort of error checking used in the formula.</SPAN></SPAN>

I’ve tried playing around with VLOOKUP, INDEX, MATCH and a combination thereof, but can’t seem to get any to work. I did find the first record; but as I pulled down the formula, I get the same record shown all the way down to the end of the Table.</SPAN></SPAN>

I know someone out there have done this before. Can someone help me or point me to a place where this might be described on how to do it?</SPAN></SPAN>

Thanks in advance to all or anyone who responded with a solution.</SPAN></SPAN>

 

[Smitty]

Sounds like a good job for a Pivot Table with Sales Rep name as a Filter.

HTH,

 

[Excelanil]

It turns out that the Table come from a Pivot Table and when the Pivot Table gets refreshed is refreshed. What I'm trying to do is use the Table as the basis for my presentation of the selected Sales Reps with some calculations involved.

 

[Smitty]

If it's already in a Pivot Table, then you should be able to create another one with the specifications that you want.

How about posting a shot of what you've got now and what you want?