EXCEL Populating multiple sheets from one sheet based on criteria 1

[johnturgoose]

This seems really easy in Access however in excel it seems a whole world of pain.

I have a sheet that is constantly update (CentreOverview). This has a colomn (TrialsUnit) with 4 differnt trials units entered as text.

I basically want to have 4 further sheets that show all the same data as (CentreOverview) but filtered by the TrialsUnit. Simple, however i want this to update automatically as further data is added to (TrialsUnit). The only solution i can see is to paste a link between all 4 individual sheets and (CentreOverview) and then then filter each sheet. This seems clumsy and with approx 3500 rows (and growing at 10 a day) in (centreoverview) would run rather slow.

Is it a case of coding required?

Any help would be much appreciated.

Thanks

John Turgoose

 

[SkipVought]

John,

You could use AutoFilter on the data sheet.

You could use the PivotTable Wizard to gen one or more pivot tables to report the subset of data you want. If you go this route, use a dynamic named range to define your data range -- faq68-1331 How can I rename a table as it changes size

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[LNBruno]

Hi, John!

We had a workbook with several dozen projects, each with its own tab and, embedded in each worksheet was the project's start and end dates. We wanted to put the dates in a new workbook (creating a snapshot). The following formula allowed us to simply copy the formula down instead of the usual "click in a cell, type an equals sign, find the value in the source workbook and press enter" to create a cell reference.

=INDIRECT("[Project_timetables_INACTIVE.xls]" & A2 & "!$E$8")

Hope this helps!


< M!ke >