filter data

[kev carter]

hi
i have a database in excell
this database is going to collect and store data over a 12 month period
as you can imagine there is going to be a lot of data collected over the 12
months
i have created 12 worksheets that i have filtered by month each of the
worksheets is linked to the main database
this means that each worksheet is goning to be the same size as main
database
is it possable to transfer data only if the date is in range i.e only send
data to jan worksheet if date is in range 1/1/04 to 31/1/04


thanks in advance

kevin

 

[Frank Kabel]

Hi
it's possible with array formulas but this will probably slow down your
spreadsheet. Why do you separate the data at all in different sheets?
If you want a specific report you may use 'Data - Filter - Advanced
Filter' and use a separate sheet as target range

 

[Dave Peterson]

I agree with Frank. Keep all your data together and use filters.

But if you have to separate them, you could run an on-demand macro that rebuilds
your reports each time you need it.

(I would use a helper column containing a formula like:
=text(a1,"yyyy-mm")

Then I could use one of the samples that Debra Dalgleish has at:
http://www.contextures.com/excelfiles.html

look for:

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- creates a list of unique items, creates
a
sheet for each item, then replaces old data with current. AdvFilterCity.xls 44
kb

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