Populate a Table From Formula

K

Karl Burrows

I have a link to an external Access database to track my time. I use the
Excel workbook (2003) to track a few details from the database. What I
would like to do is create a formula (probably an array?) that would fill in
a table/rows below with the data that matches the formula criteria of date
range. Right now, I have to create formulas to match each row of data in
the imported sheet, so if the data grows beyond the number of rows I created
the formulas, then I have to edit the worksheet again. Most of the time, it
has already passed the last row before I realize I need to expand the rows
of formulas.

It just seems there must be a better way of pulling data to populate rows.

Thanks!!
 
K

Karl Burrows

I have the query running, but there are several worksheets that use this
database import, so asking for a date each time is asking for trouble from
the people that open them. Also, some formulas look at current dates and
some look at the entire history, so that wouldn't work. It still does solve
the formula problem, just potentially reduces the number of data rows that
are imported.

Thanks!
 
J

Jamie Collins

...
I have the query running, but there are several worksheets that use this
database import, so asking for a date each time is asking for trouble from
the people that open them.
Click to expand...

Use a second query e.g. if the query that all the worksheet use looks
like this:

SELECT * FROM MyTable;

then create another that looks like this:

SELECT * FROM MyTable
WHERE MyDate BETWEEN
#01 JUN 2004# AND #30 JUN 2004#;

This is the kind of thing databases do to earn their living. Better to
hit the database twice than mess around with slow worksheet formulas.

Jamie.

--
 
Top