Organizing Data Over Multiple Worksheets

**ErinCalAtlantic** · 06-06-2011, 04:54 PM

I would like to create a workbook that allows me to type data into a master sheet (3 columns Name, State, Department) I would then like the information to populate onto other worksheets according to the data. Example:

Column1 Column2 Column3
Name State Dept
Dan CA ops
Erin TX admin
Karen CA Admin

I would like worksheet entitled "California" to pull all of the rows onto it from the people that are marked "CA" in the state column so that it would look like this:

Name State Dept
Dan CA ops
Karen CA Admin

and so on for each State worksheet

Is this possible? The only way I could think to do it would leave gaps in the lists (say if I was using an "IF" command it would pull row 5 to row 5 and leave blank rows for false statements)

**Miraun** · 06-06-2011, 05:11 PM

Hi Erin;

Depending on the intended use of the worksheets, here is a quick workaround to any dynamic formula to create spreadsheets quickly based upon the data within the master file.

Using your data, create a pivot table of the master sheet.

Use the row labels as the criteria you're looking to create the individual worksheets for
In this instance, State
And then put the either of the other criteria in the Data Area (Sum area in 2007/2010)
Dept and Name values.

Double click on the Total # that pops up in the pivot table, and it will create a separate spreadsheet with just the data that corresponds to those values. You can simply go down the list, keep clicking, and keep creating new sheets.

However, these new tables that are created are a point in time capture of the current Pivot Table. If you're looking for something more dynamic and self-updating, I can take a stab at it in the morning.

**Haseeb Avarakkan** · 06-06-2011, 07:10 PM

Hello, See the attached.

You can change the State Code in the Filter sheet to get the corresponding details, instead of creating a tab for each state.

**ErinCalAtlantic** · 06-07-2011, 12:22 PM

Originally Posted by Haseeb A

Hello, See the attached.

You can change the State Code in the Filter sheet to get the corresponding details, instead of creating a tab for each state.

AHHH that is exactly what I needed thank you so much I could literally cut and paste my info into the data sheet

I am assuming I can just adjust the formula to read the departments the same as the states for those tabs. I see what you are saying about the not needing the tabs but my boss likes to see the tabs on there so I will give him both options

**ErinCalAtlantic** · 06-07-2011, 01:21 PM

OK So I guess I just am not nearly as good as I had hoped how do I do the same thing only to pull over the departments instead of states

**Haseeb Avarakkan** · 06-08-2011, 03:25 AM

Hello Erin,

On the filter tab, you can select the options to display, name, State or Dept. or your own choices depends on your data.

See the attached.