IF Statement????

[RWilson130]

I have a report that I do every week and the data within this report changes
from week to week. Once I have all of the data for the State, I then have to
break out this information into a separate tab (Central, Dallas, etc). The
Sales rep is indicated within the data (R Wilson, J Doe, etc.). Let's say
that the tab named Central is associated with R Wilson, should I use an IF
Statement to pull over all of the data from the State level to the tab named
Central? If so, can you tell me how this is done? If not, can you help me?

Any help would be VERY MUCH APPRECIATED!!!

Thanks!

Rwilson130

 

[Jim Thomlinson]

Have you considered using a pivot table. They are often the ideal solution
for breaking infomration out of record sets...

 

[RWilson130]

We do use pivot table quite a bit, but my boss wants this info broke out by
territory. There is quite a bit of data in these reports and up to now I
have been doing a copy/paste to break this info down to the territory level.
I was hoping that there might be a more automative way to populate all of the
info to each tab by Sales rep.

Thaks any way for your help!!

 

[Jim Thomlinson]

The probem that you tend to run into breading upa a record set into subsets
is that you never knwo who big the subset is going to be. Using array
formulas, sumproduct formulas database functions and such you can get
aggregations of the data by very specific critera.

The final option is to use GetExternal Data and link a MSQuery in one sheet
back to the source data in another sheet. The query will die is you move the
sheet from one place to another.

 

[Dave Peterson]

I'd keep the data in one worksheet and apply Autofilter to it. Then I could use
the dropdown arrow to show just the rows that I want.

But if you want...

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm