Extracting information from a database

[paulrm906]

I have just set up a database with the date in the first colmn and then
about 50 colmns full of data and of cause a heading, and I would like to
have a new page setup with all this information in colmn A. Then for
example in cell B1 will be a date 15-June 05 and in cell C1 15-Oct 05
and I need a formula to extract the information from database starting
from the 1st (B1) date and ending with 2nd (C1) date.

Thankyou in advance Paul Maynard.

 

[Nick Hodge]

Paul

Check this out

http://www.nickhodge.co.uk/gui/datamenu/dataexamples/externaldataexamples.htm

You don't say what your 'database' is in, but as long as it has ODBC
functionality this will work

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[Debra Dalgleish]

You can use an Advanced Filter to extract the data. There are
instructions here:

http://www.contextures.com/xladvfilter01.html#ExtractWs

And an example of date criteria at the bottom of the page:

http://www.contextures.com/xladvfilter01.html#Criteria

 

[paulrm906]

You can use an Advanced Filter to extract the data. There are
instructions here:

http://www.contextures.com/xladvfilter01.html#ExtractWs

And an example of date criteria at the bottom of the page:

http://www.contextures.com/xladvfilter01.html#Criteria

Thanks Debra

I will give a go tomorrow but I still need to find if there is a
formula to extract data from one date to another date has my boss does
not know a lot about excel. I would like it if he could just enter the
dates from and to and the information appears just like magic.

Thanks

Paul Maynard

 

[Max]

... would like it if he could just enter the
dates from and to and the information appears just like magic.

Here's an option using non array formulas
which could also deliver the desired results ..

Sample construct at:
http://cjoint.com/?locKxXkPib
Extracting_information_from_a_database_paulrm906_gen.xls

In Sheet1
---------
Assume source table is in cols A to AZ, data from row1 down
(Dates are in col A)

Using an empty col to the right, say, col BA?

Put in BA2:
=IF(OR(A2="",Sheet2!$B$1="",Sheet2!$C$1=""),"",
IF(AND(A2>=Sheet2!$B$1,A2<=Sheet2!$C$1),ROW(),""))

Copy BA2 down to say, BA30,
to cover the max expected extent of source data
(Leave BA1 empty)

In Sheet2
-----------
Start Date & End Date will be input in B1:C1
Paste the same col headers from Sheet1 into A2:AZ2

Put in A3:
=IF(ISERROR(SMALL(Sheet1!$BA:$BA,ROWS($A$1:A1))),"",
INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!$BA:$BA,ROWS($A$1:A1)),Sheet1!$BA:$BA,0)
))

Copy A3 across to AZ3 (across as many cols as required),
then fill down to AZ30 (row30)
(cover the same row extent as done in Sheet1's col BA)

Format col A as date

Sheet2 will return only the lines from Sheet1 with dates falling between (&
inclusive of) the start & end dates entered into B1:C1, with all results
neatly bunched at the top.

 

[Max]

Typo in line:

Assume source table is in cols A to AZ, data from row1 down

should be:

... data from row2 down

(col headers are in row1)

 

[paulrm906]

Thanks Max

I don't know it works but it worked for but admit it did take me a few
hours. the only draw back is that it really does slow my computer down
at work and takes for ever to update it self however I will be trying
this at home soon to see if it actually works quicker there.

Thanks again

Paul Maynard

 

[Max]

You're welcome, Paul !

One way to manage the "slowness" is to set the calc mode to "Manual" (via
Tools > Options > Calculation tab), and then press F9 to calculate only when
required (eg: when all necessary inputs are made). This should speed up
input/updating work in the file (calculate only when done/required)

 

[paulrm906]

Thanks again Max

I am back in thye office tomorrow morning and if i have time i will
test your theory on manual update.

Paul

 

[Max]

Thanks for feedback, Paul!